Getting PPMT/IPMT & PV/CUMIPMT to work with 2 rates of interest

[neylon]

Hi Everyone

So just for context, i have a fairly good knowledge of excel, but have only just started looking at mortgages for the first time in my life. Because I like excel, I wanted to build my own mortgage calculator that shows me how the principle is paid off over time/how much of my monthly payments are just going on interest. Something i can just play around with to see how mortgages work.

However, i've gotten stuck on how to properly use PPMT/IPMT when there is an introductory period at a lower rate, then the remainder at a higher rate.

So my assumptions at the moment = 25y Lease, 5y@2%, 20y@5%, average 4.4%,
Total principle of £363,523. This is calculated using the PV function involving 25y mortgage @ 4.4% on a £2,000pm payment budget i've decided on.
Total Interest of £236,477. This is calculated using CUMIPMT, again on 4.4% average 25y mortgage on the £363,523 principle.
Total Principle + Interest = £600,000 = £2,000*(25y*12), so all good.

Then i've built an amortisation schedule across 300 columns (25y*12) so that i can see how the principle + interest is paid off over time.
Principle payment is calculated using PPMT and currently using an IF statement to choose the interest rate depending on which year it is.
Interest payment is calculated using IPMT with basically the same formula.

However the totals for these two rows come to Principle £380,954, Interest £221,522, total £602,477, which is an imbalance of £2,477 i assume from quirks of how the interest rate is applied over the course of the schedule.

How can i make these two different methods of reaching the total Principle + Interest balance?

if it helps, my PPMT and IPMT formulas across the schedule are currently: =(PPMT(IF(H$1<=$B$7,$B$6,$B$8)/12,H$2,$B$4*$B$5,-$B$10,0,0)
where H1=year(1-25), B7=introductory period length (5y), B6=Introductory rate(2%), B8=subsequent rate(5%), H2=payment period(1-300),B4=Loan Length (25y), B5=payment p.a. (12), B10=loan value(363,523)

Thank you so much for your help!
Ben

 

[Dave Patton]

Book1
	A	B	C	D
1	Principal	$500,000.00
2	Rate 1	2%	0.1667%
3	Rate 2	5%	0.4167%
4	Term	25
5	Payment	$2,575.07
6				500,000.00
7	1	2,575.07	833.33	498,258.26
8	2	2,575.07	830.43	496,513.62
2a

Cell Formulas
Range	Formula
C2:C3	C2	=B2/12
D6	D6	=B1
B7:B8	B7	=$B$5
C7:C8	C7	=ROUND($C$2*D6,2)
D7:D8	D7	=D6-B7+C7

A blended or average rate will probably not yield the results that you require.
You could consider preparing an amortization chart (see below) and assemble data from that schedule.
I built the schedule and then used Goal Seek to calculate the payment.

 

[joeu2004]

(I assume that you want to discuss a mortgage, not a lease [sic]. The difference affects the value of the "type" parameter. You used type=0, which is consistent for a mortgage/loan.)

We cannot use CUMIPMT and CUMPRINC to calculate interest and principal of the separate terms of the loan (5 years and 20 years) because they calculate the fixed regular payment internally based on a final balance ("fv") of zero.

The important point is: the "pv" and "fv" are different for the two terms.

For second term (last 20 years), the "pv" is the balance after the first term (first 5 years).

And for the first term (first 5 years), the "fv" is not zero.

The following implementation might give you some insight.

Book1
	A	B	C	D	E	F
1		Ben			Goal Seek
2	Loan	363,523.00			388,339.08
3	Term1	5	yr		5	yr
4	Term2	20	yr		20	yr
5	Rate1	2.00%			2.00%
6	Rate2	5.00%			5.00%
7	Pmt	2,000.00	monthly		2,000.00	monthly
8
9	Bal1	275,626.89			303,050.63
10	Bal2	-74,390.72			0.00
11	Term2	205.35	mo		3.77%	avg rate
Sheet1

Formulas:
B9:  =FV(B5/12,B3*12,B7,-B2)
B10: =FV(B6/12,B4*12,B7,-B9)
B11: =NPER(B6/12,B7,-B9)
B12: =B11/12
E9:  =FV(E5/12,E3*12,E7,-E2)
E10: =FV(E6/12,E4*12,E7,-FV(E5/12,E3*12,E7,-E2))
E11: =12*RATE((E3+E4)*12,E7,-E2)
F11: =E10

In columns B:C, we can see that with a fixed regular payment of 2,000 for both terms, the estimated initial loan amount of 363,523 results in a negative balance (-74,390.72) at the end of the second term.

That is because the ending balance after the first term in B9 is FV(B5/12,B3*12,B7,-B2), which is 275,626.89.

And that is the initial balance ("pv") for the second term.

So the ending balance after the second term in B10 is FV(B6/12,B4*12,B7,-B9).

In order for the ending balance after the second term to be zero, the length of the second term in B11 is NPER(B6/12,B7,-B9), which is about 205 months, a little more than 17 years (B12).

-----

In columns E:F, we might use Goal Seek to determine the initial loan amount (E2) that results in a balance of zero at the end of the second term (E10), assuming a fixed regular payment of 2000 (E7) for both terms.

The Goal Seek set up is:

Set cell: E10
To value: 0
By changing: E2

Thus, the initial loan is 338,339.08.

The ending balance after the first term and the initial balance for the second term (E9) is FV(E5/12,E3*12,E7,-E2), which is 303,050.63.

We might use that in calculating interest paid in each term.

But we can calculate the ending balance after the second term directly in E10 with the expression FV(E6/12,E4*12,E7,-FV(E5/12,E3*12,E7,-E2)).

(Arguably, we can replace -FV(E5/12,E3*12,E7,-E2) with simply FV(E5/12,E3*12,E7,E2), which returns a negative value. But I always write my financial functions so that they return positive values. A personal preference.)

The average annual rate (E11) is 12*RATE((E3+E4)*12,E7,-E2), which is about 3.77%, based on an initial cash flow of -388,339.08 and 300 payments of 2000.

-----

We can calculate the total interest and the interest paid in each term as follows. The following also demonstrates why we cannot use CUMIPMT.

Book1
	E	F	G
14	211,660.92	Total Int
15
16	34,711.54	Int1
17	176,949.37	Int2
18	211,660.92	Total Int	True
19
20	20,063.77	Int1	-14,647.77
21	176,949.37	Int2	1.16E-09
22	197,013.15	Total Int	-14,647.77
Sheet1

E14: =(E3+E4)*12*E7-E2
E16: =E7*E3*12-(E2-E9)
E17: =E7*E4*12-E9
E18: =SUM(E16:E17)
E20: =-CUMIPMT(E5/12,E3*12,E2,1,E3*12,0)
E21: =-CUMIPMT(E6/12,E4*12,E9,1,E4*12,0)
E22: =SUM(E20:E21)
F18: =E18=E14
F20: =E20-E16
F21: =E21-E17
F22: =E22-E18

The total interest in E14 is (E3+E4)*12*E7-E2, which is 300 payments of 2000 less the initial principal of 338,339.08.

The interest paid in the first term in E16 is E7*E3*12-(E2-E9), which is 60 payments of 2000 less the principal paid in the first term, which is the initial principal less the ending balance after the first term.

The interest paid in the second term in E17 is E7*E4*12-E9, which is 240 payments of 2000 less the remaining principal (ending balance) after the first term.

Note that the sum in E18 is the same as the total calculated in E14, as shown in G18 (E18=E14).

In contrast, the calculations in E20:E22 demonstrate that we cannot use CUMIPMT.

For the second term, the CUMIPMT calculation in E21 returns essentially the amount that we calculated in E17. The difference of 1.16E-09 in G21 is an infinitesimal difference, probably due to binary arithmetic anomalies.

However, for the first term, the CUMIPMT calculation in E20 returns the wrong amount (compare with E16). The reason, again, is: CUMIPMT assumes that the ending balance of the first term is zero. It is not.

 

[joeu2004]

PS.... For a monthly amortization schedule, do not use PPMT and IPMT.

Yes, you could, if you use the appropriate "pv" and "fv" amounts as explained in my previous posting, and the appropriate relative payment numbers (not 1 to 240, not 61 to 300, for the second term). But why bother?

Instead, simply calculate the monthly interest paid directly from the previous balance, using the appropriate monthly rate for the term (2%/12 or 5%/12).

And calculate the monthly principal paid from the monthly payment (2000) less interest paid.

Calculate the resulting balance from the previous balance less principal paid.

I hope that description is sufficient. Let me know if you turnkey formulas.

 

[joeu2004]

The Goal Seek set up is:
Set cell: E10
To value: 0
By changing: E2

We do not need to use Goal Seek.

A formula for the initial loan principal in E2 is:

=( E7*(1+G6)^G4 * ((1+G5)^G3 - 1)/G5 + E7*((1+G6)^G4 - 1)/G6 ) / ( (1+G5)^G3 * (1+G6)^G4 )

where:

E3:    5     G3: =E3*12
E4:   20     G4: =E4*12
E5: 2.00%    G5: =E5/12
E6: 5.00%    G6: =E6/12
E7: 2000

 

[joeu2004]

(Sorry for the incessant posting, but this forum does not permit us to delete our mistakes.)

#VALUE!

[....]
The Goal Seek set up is:
Set cell: E10
To value: 0
By changing: E2

Again, we do not need to use Goal Seek.

But ferheavenssake, the formula in E2 is simply:

=PV(E5/12, E3*12, -E7, -PV(E6/12, E4*12, -E7))

And the formula in E9 is:

=PV(E6/12, E4*12, -E7)

See my posting #3 for other comments. (But obviously there is no formula in F11. Klunk!)

 

[neylon]

(Sorry for the incessant posting, but this forum does not permit us to delete our mistakes.)




Again, we do not need to use Goal Seek.

But ferheavenssake, the formula in E2 is simply:

=PV(E5/12, E3*12, -E7, -PV(E6/12, E4*12, -E7))

And the formula in E9 is:

=PV(E6/12, E4*12, -E7)

See my posting #3 for other comments. (But obviously there is no formula in F11. Klunk!)

Thank you so much for your detailed response. Sorry for taking a while to get back to you i asked the question before going to bed and then have been at work.

you've really helped me understand everything, and i think i've got it in a place where i'm happy with the output.

Thanks again

 

[joeu2004]

You're welcome. Not my clearest explanations; too many mistakes. But it sounds like you were able to sort things out. Good for you!

Yet another errata....

you could [use PPMT and IPMT], if you use [...] the appropriate relative payment numbers (not 1 to 240, not 61 to 300, for the second term)

Aarrgghh! That should say: ``1 to 240, not 61 to 300``. That is, the first "not" should be omitted.

I guess that's an "improvement" for me. Usually, I omit the word "not" when it's essential. Klunk!