As I suspected, we can indeed derive formulas to calculate the initial payment directly and exactly, not an estimate.
Granted, the formulas are complicated. So as I said before, my previous solution using Goal Seek or Solver might be easier.
For the direct calculation....
First some important assumptions, which I noted in my previous posting.
1. The monthly rate should be derived from the annual rate as follows: (1+9%)^(1/12)-1. This is based on msvariar's assertion that without the 10% annual increase, the monthly payment would be 30882 [sic]. That is derived from the formula =PMT((1+9%)^(1/12)-1, 5*12, 0, -2307936, 0), which results in 30882.4752290111.
2. IMHO, payments should be at the beginning of periods, not at the end, so that we earn a return on all payments. This is contrary to msvariar's implicit assumption that payments are at the end of periods. So the correct formula should be =PMT((1+9%)^(1/12)-1, 5*12, 0, -2307936, 1). That results in 30661.4879718442.
Based on those assumptions, the initial payment can be calculated as follows.
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Annual[/TD]
[TD="align: right"]Monthly[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]Yield[/TD]
[TD="align: right"]9.00%[/TD]
[TD="align: right"]0.7207%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]Nper[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]%Pmt inc[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]FV goal[/TD]
[TD="align: right"]2,307,936.00[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]Init pmt[/TD]
[TD="align: right"]294,545.69[/TD]
[TD="align: right"]25,526.53[/TD]
[/TR]
</tbody>[/TABLE]
Formulas:
C2: =(1+B2)^(1/12) - 1
C3: =B3*12
B6: =B5 / SUMPRODUCT((1+B2)^(B3+1-ROW(A1:INDEX(A:A,B3,1))) * (1+B4)^(ROW(A1:INDEX(A:A,B3,1))-1))
C6: =PMT(C2,12,0,-B6*(1+B2),1)
The correctness of the initial annual payment in B6 can be demonstrated as follows:
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]Pmt#[/TD]
[TD="align: right"]Pmt/yr[/TD]
[TD="align: right"]End Bal[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Pmt/mo[/TD]
[TD="align: right"]%Pmt inc[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]294,545.69[/TD]
[TD="align: right"]321,054.80[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]324,000.26[/TD]
[TD="align: right"]703,110.02[/TD]
[TD="align: right"][/TD]
[TD="align: right"]28,079.18[/TD]
[TD="align: right"]10.00%[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]356,400.29[/TD]
[TD="align: right"]1,154,866.23[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30,887.10[/TD]
[TD="align: right"]10.00%[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]392,040.31[/TD]
[TD="align: right"]1,686,128.13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]33,975.81[/TD]
[TD="align: right"]10.00%[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]431,244.35[/TD]
[TD="align: right"]2,307,936.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37,373.39[/TD]
[TD="align: right"]10.00%[/TD]
[/TR]
</tbody>[/TABLE]
Formulas:
F9: =B6
G9: =(N(G8)+F9)*(1+$B$2)
I9: =PMT($C$2, 12, N(G8), -G9, 1)
F10: =F9*(1+$B$4)
J10: =I10/I9 - 1
Copy G9 into G10. Copy I9 into I10. Copy F10:J10 into F11:J13.
The correctness of the initial monthly payment in C6 can be demonstrated with a table similar to the one in my previous posting, to wit:
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]Pmt#[/TD]
[TD="align: right"]Pmt[/TD]
[TD="align: right"]End Bal[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]25,710.51[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]51,606.32[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]293,230.88[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]25,526.53[/TD]
[TD="align: right"]321,054.80[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]28,079.18[/TD]
[TD="align: right"]351,650.31[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]28,079.18[/TD]
[TD="align: right"]382,466.32[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]67[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]37,373.39[/TD]
[TD="align: right"]2,254,047.59[/TD]
[/TR]
[TR]
[TD="align: center"]68[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]37,373.39[/TD]
[TD="align: right"]2,307,936.00[/TD]
[/TR]
</tbody>[/TABLE]
Formulas:
A9: =N(A8)+1
B9: =C6
C9: =(N(C8)+B9)*(1+$C$2)
B10: =IF(MOD(A10-1,12)=0, B9*(1+$B$4), B9)
Copy A9 into A10. Copy C9 into C10. Copy A10:C10 into A11:C68.
-----
For the curious, the mathematical derivation of the formula in B6 (initial annual payment) is as follows.
PMT*(1+10%)^0*(1+9%)^5 + PMT*(1+10%)^1*(1+9%)^4 +...+ PMT*(1+10%)^4*(1+9%)^1 = FV (2,307,936)
PMT = FV / ( (1+10%)^0*(1+9%)^5 + (1+10%)^1*(1+9%)^4 +...+ (1+10%)^4*(1+9%)^1 )
Note that (1+10%)^0 = 1. I include it in order to make the exponential series clearer.