Calculate Future Outstanding Loan Balance for Floating Interest Rate

[brandonej]

Hello all! I'm trying to calculate the future outstanding balance for a loan with a floating interest rate.

Since the rate varies, I don't believe the FV function applies (I could be wrong).

Please refer to the attached image for context.

Any help would be appreciated.

 

[kvsrinivasamurthy]

For 1st year interest is 4500. But payment is shown 4467. How is it.
If principle is taken as 100000-1613 then interest is 4427.

 

[brandonej]

For 1st year interest is 4500. But payment is shown 4467. How is it.
If principle is taken as 100000-1613 then interest is 4427.

I used the CUMIPMT function to calculate the interest payment:

=CUMIPMT($C$10/12,$D$5*12,$D$4,C9*12-11,C9*12,0)

Similarly, I used the CUMPRINC function to calculate the principal payment:

=CUMPRINC(C10/12,$D$5*12,$D$4,C9*12-11,C9*12,0)

I'm not 100% sure that it's the right approach, but that's how I came up with those numbers.

 

[StephenCrump]

Your formulae work correctly in the first year, but as you copy to the right, they will point to different interest rates.

=CUMIPMT($C$10/12,$D$5*12,$D$4,C9*12-11,C9*12,0)
=CUMPRINC(C10/12,$D$5*12,$D$4,C9*12-11,C9*12,0)

The calculations below assume that when the interest rate changes, payments are recalculated based on the new interest rate, balance outstanding, and unexpired term.

(There are other ways you could set out the calculations)

	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4				100,000.00
5				30
6
7
8
9		Year	1	2	3	4	5	6	7	8	9	10	11
10		Interest rate	4.50%	4.75%	5.00%	5.25%	4.90%	4.85%	5.00%	5.00%	5.00%	5.00%
11
12		Start balance	100,000.00	98,386.77	96,769.94	95,143.17	93,499.98	91,673.77	89,742.17	87,756.15	85,668.52	83,474.09	81,167.38
13		Payments	6,080.22	6,255.31	6,428.32	6,599.04	6,367.06	6,335.22	6,428.03	6,428.03	6,428.03	6,428.03
14
15		Principal	1,613.23	1,616.83	1,626.77	1,643.19	1,826.21	1,931.60	1,986.02	2,087.63	2,194.44	2,306.71
16		Interest	4,467.00	4,638.47	4,801.55	4,955.85	4,540.85	4,403.62	4,442.01	4,340.40	4,233.59	4,121.32
17			6,080.22	6,255.31	6,428.32	6,599.04	6,367.06	6,335.22	6,428.03	6,428.03	6,428.03	6,428.03
18
19		Your P	1,613.23	1,617.99	<-- uses 4.75%
20		Your I	4,467.00	4,392.89	<-- uses 4.5%
21		Repayment	6,080.22	WRONG
22
23		Compare	6,080.22	6,255.31	6,428.32	6,599.04	6,367.06	6,335.22	6,428.03	6,428.03	6,428.03	6,428.03
24
Sheet1

Cell Formulas
Range	Formula
C12	C12	=D4
D12:M12	D12	=FV(C10/12,12,C13/12,-C12)
C13:L13	C13	=12*PMT(C10/12,($D5+1-C9)*12,-C12)
C15:L15	C15	=C12-D12
C16:L16	C16	=C13-C15
C17:L17,C21	C17	=SUM(C15:C16)
C19:D19	D19	=-CUMPRINC(D10/12,$D$5*12,$D$4,D9*12-11,D9*12,0)
C20:D20	D20	=-CUMIPMT($C$10/12,$D$5*12,$D$4,D9*12-11,D9*12,0)
C23:L23	C23	=12*PMT(C10/12,($D5+1-C9)*12,-C12)

 

[brandonej]

Your formulae work correctly in the first year, but as you copy to the right, they will point to different interest rates.

=CUMIPMT($C$10/12,$D$5*12,$D$4,C9*12-11,C9*12,0)
=CUMPRINC(C10/12,$D$5*12,$D$4,C9*12-11,C9*12,0)

The calculations below assume that when the interest rate changes, payments are recalculated based on the new interest rate, balance outstanding, and unexpired term.

(There are other ways you could set out the calculations)

	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4				100,000.00
5				30
6
7
8
9		Year	1	2	3	4	5	6	7	8	9	10	11
10		Interest rate	4.50%	4.75%	5.00%	5.25%	4.90%	4.85%	5.00%	5.00%	5.00%	5.00%
11
12		Start balance	100,000.00	98,386.77	96,769.94	95,143.17	93,499.98	91,673.77	89,742.17	87,756.15	85,668.52	83,474.09	81,167.38
13		Payments	6,080.22	6,255.31	6,428.32	6,599.04	6,367.06	6,335.22	6,428.03	6,428.03	6,428.03	6,428.03
14
15		Principal	1,613.23	1,616.83	1,626.77	1,643.19	1,826.21	1,931.60	1,986.02	2,087.63	2,194.44	2,306.71
16		Interest	4,467.00	4,638.47	4,801.55	4,955.85	4,540.85	4,403.62	4,442.01	4,340.40	4,233.59	4,121.32
17			6,080.22	6,255.31	6,428.32	6,599.04	6,367.06	6,335.22	6,428.03	6,428.03	6,428.03	6,428.03
18
19		Your P	1,613.23	1,617.99	<-- uses 4.75%
20		Your I	4,467.00	4,392.89	<-- uses 4.5%
21		Repayment	6,080.22	WRONG
22
23		Compare	6,080.22	6,255.31	6,428.32	6,599.04	6,367.06	6,335.22	6,428.03	6,428.03	6,428.03	6,428.03
24
Sheet1

Cell Formulas
Range	Formula
C12	C12	=D4
D12:M12	D12	=FV(C10/12,12,C13/12,-C12)
C13:L13	C13	=12*PMT(C10/12,($D5+1-C9)*12,-C12)
C15:L15	C15	=C12-D12
C16:L16	C16	=C13-C15
C17:L17,C21	C17	=SUM(C15:C16)
C19:D19	D19	=-CUMPRINC(D10/12,$D$5*12,$D$4,D9*12-11,D9*12,0)
C20:D20	D20	=-CUMIPMT($C$10/12,$D$5*12,$D$4,D9*12-11,D9*12,0)
C23:L23	C23	=12*PMT(C10/12,($D5+1-C9)*12,-C12)

Thank you. Also, great catch! I didn't realize I made that mistake.