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.
| A | B | C |
1 | | Annual | Monthly |
2 | Yield | 9.00% | 0.7207% |
3 | Nper | 5 | 60 |
4 | %Pmt inc | 10.00% | |
5 | FV goal | 2,307,936.00 | |
6 | Init pmt | 294,545.69 | 25,526.53 |
<tbody>
</tbody>
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:
| E | F | G | H | I | J |
8 | Pmt# | Pmt/yr | End Bal | | Pmt/mo | %Pmt inc |
9 | 1 | 294,545.69 | 321,054.80 | | 25,526.53 | |
10 | 2 | 324,000.26 | 703,110.02 | | 28,079.18 | 10.00% |
11 | 3 | 356,400.29 | 1,154,866.23 | | 30,887.10 | 10.00% |
12 | 4 | 392,040.31 | 1,686,128.13 | | 33,975.81 | 10.00% |
13 | 5 | 431,244.35 | 2,307,936.00 | | 37,373.39 | 10.00% |
<tbody>
</tbody>
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:
| A | B | C |
---|
8 | Pmt# | Pmt | End Bal |
9 | 1 | 25,526.53 | 25,710.51 |
10 | 2 | 25,526.53 | 51,606.32 |
|
|
|
|
19 | 11 | 25,526.53 | 293,230.88 |
20 | 12 | 25,526.53 | 321,054.80 |
21 | 13 | 28,079.18 | 351,650.31 |
22 | 14 | 28,079.18 | 382,466.32 |
|
|
|
|
67 | 59 | 37,373.39 | 2,254,047.59 |
68 | 60 | 37,373.39 | 2,307,936.00 |
<tbody>
</tbody>
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.