Interest rate calculation with payment increases

[tbarnett]

Hello All!


This is my first time posting on the forum, as my previous 6 hours of searching has turned out to be fruitless. I've tried everything from searching this forum, the rate function, even the Goal Seek function. The rate function seemed a reasonable option however it doesn't allow rent increases. My apologies in advance if this has been solved and I missed it and thanks to everyone for your help!


I have a capital lease amortization schedule with annual increases to monthly rent that I am trying to solve for an interest rate such that the balance nets to zero at the end of the term. I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month. The present value is known, payment term is known, future value is zero, and the payment amounts increase annually. These assumptions may change for new leases so ideally the solution would be dynamic, adjusting for shorter/longer terms, etc. Here is an example of my assumptions:
Payment start date: 8/1/13
Term end date: 10/31/2025
Rent length in months: 144
1st months rent: $500,000 - payments are due at the beginning of the month and are paid monthly
Annual rent escalation: 3% - i.e. 1st 12 months is $500k/month, 2nd 12 months at $515K, etc.
Beginning NPV: $75M
Ending value: $0
Imputed annual interest rate: UNKNOWN


I'm not sure if this is relevant, but the monthly payment is allocated between principal and interest. Monthly interest expense is calculated as the current balance * (imputed interest rate / 12).
Currently, I've plugged the interest rate such that my ending balance is 0, however I was hoping to find help in calculating it on the fly as opposed to manually plugging it.

Any idea how to calculate annual interest rate with these inputs? Is there a way to make the rate function work with payment increases?


Please let me know if I can clarify or additional detail is needed.
Thanks so much everyone!


Best,
Tyler

 

[joeu2004]

I have a capital lease amortization schedule with annual increases to monthly rent that I am trying to solve for an interest rate such that the balance nets to zero at the end of the term. I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month. [....] Here is an example of my assumptions:
Payment start date: 8/1/13
Term end date: 10/31/2025
Rent length in months: 144
1st months rent: $500,000 - payments are due at the beginning of the month and are paid monthly
Annual rent escalation: 3% - i.e. 1st 12 months is $500k/month, 2nd 12 months at $515K, etc.
Beginning NPV: $75M
Ending value: $0
Imputed annual interest rate: UNKNOWN

We can use the IRR function. Download "varpmt int rate.xls" (click here) [1]. It provides a 30-year template. The table below shows part of the content. It is explained below.

	A	B		D		F	G	H	I	J
1	Loan	$75,000,000.00		IRR_Data		Pmt#	Pmt_Due	Pmt	Int	Bal
2	#_Pmts	147		$75,000,000.00						$75,000,000.00
3	1st_Pmt_Date	8/1/2013		-$500,000.00		1	8/1/2013	$500,000.00	$148,659.92	$74,648,659.92
4	Last_Pmt_Date	10/1/2025		-$500,000.00		2	9/1/2013	$500,000.00	$147,963.52	$74,296,623.45
5	End_Loan_Date	10/31/2025		-$500,000.00		3	10/1/2013	$500,000.00	$147,265.74	$73,943,889.18
6	1st_Pmt_Amt	$500,000.00		-$500,000.00		4	11/1/2013	$500,000.00	$146,566.57	$73,590,455.76
7	%_Pmt_Incr	3.00%		-$500,000.00		5	12/1/2013	$500,000.00	$145,866.02	$73,236,321.78
8	Monthly_Rate	0.198213%		-$500,000.00		6	1/1/2014	$500,000.00	$145,164.08	$72,881,485.86
9	Annual_Rate	2.38%		-$500,000.00		7	2/1/2014	$500,000.00	$144,460.75	$72,525,946.61
10				-$500,000.00		8	3/1/2014	$500,000.00	$143,756.02	$72,169,702.63
11	Last_Pmt_Row	149		-$500,000.00		9	4/1/2014	$500,000.00	$143,049.90	$71,812,752.53
12				-$500,000.00		10	5/1/2014	$500,000.00	$142,342.38	$71,455,094.91
13				-$500,000.00		11	6/1/2014	$500,000.00	$141,633.45	$71,096,728.36
14				-$500,000.00		12	7/1/2014	$500,000.00	$140,923.12	$70,737,651.48
15				-$515,000.00		13	8/1/2014	$515,000.00	$140,211.39	$70,362,862.87

<tbody>
</tbody>

We only need columns A, B and D. The amortization schedule in columns F through J demonstrates correctness. It could also be used with Goal Seek to derive the monthly interest rate in B8.

Usage....

Fill in the following constants: Loan amount (B1), #Pmts (months; B2), 1st Pmt Date (B3), 1st Pmt Amt (B6), and %Pmt Incr (every 12 months; B7).

The monthly and annual interest rates are calculated in B8 and B9.

For posterity, the formulas in columns B and D are as follows:

B4: =EDATE(B3,B2-1)
B5: =EOMONTH(B4,0)
B8: =IRR(D2:INDEX(D:D,B11))
B9: =12*B8
B11: =MATCH(2,1/D1:D362)

D2: =B1
D3: =-B6
D4: =IF(ROWS($D$3:D4)>$B$2,"",IF(MOD(ROWS($D$4:D4),12)=0,D3*(1+$B$7),D3))

Copy D4 down through D362.

Note that B11 must be array-entered: press ctrl+shift+Enter instead of just Enter.

The formulas for the amortization schedule in columns F through J are as follows:

F3: 1
G3: =B3
H3: =B6
I3: =IF(F3="","",J2*$B$8)
J3: =IF(F3="","",J2-H3+I3)

F4: =IF(OR(F3="",F3=$B$2),"",F3+1)
G4: =IF(F4="","",EDATE(G3,1))
H4: =IF(F4="","",IF(MOD(F4-1,12)=0,H3*(1+$B$7),H3))

Copy F4:H4 down through F362:H362. Copy I3:J3 down through I362:J362.

For comparison, do the following to use Goal Seek to compute the monthly interest rate:

1. Copy to a new worksheet.
2. Change $B$8 to $B$13 in I3, and copy I3 down through I362.
3. Set the Goal Seek fields as follows:
   • Set Cell: J149
   • To Value: 0
   • By Changing: B13

Note that the Set Cell row (149) is derived from the Last Pmt Row number in B11.


-----
[1] https://app.box.com/s/axpnx7ua54tzppgft9ry

 

[tbarnett]

Re: Interest rate calculation with payment increases SOLVED

That's it! Thanks so much joeu2004!!!

 

[joeu2004]

We can use the IRR function. [....] We only need columns A, B and D.

If you would like to avoid enumerating the IRR input in column D, you could replace the IRR formula in B8 with =varpmtRate(B2,B1,B6,B7). You can also delete the array-entered formula in B11. The VBA function varpmtRate is:

Function varpmtRate(npmt As Long, myPV As Double, _
    ByVal myPmt As Double, ByVal pmtIncr As Double) As Double
Dim i As Long
ReDim v(0 To npmt) As Double
myPmt = -Abs(myPmt)
pmtIncr = 1 + pmtIncr
v(0) = Abs(myPV)
v(1) = myPmt
For i = 2 To npmt
    If (i - 1) Mod 12 = 0 Then myPmt = myPmt * pmtIncr
    v(i) = myPmt
Next
varpmtRate = WorksheetFunction.IRR(v)
End Function

For comparison, download the updated file "varpmt int rate.xls" (click here) [1]. I left the IRR formula in B8, and I put the varpmtRate formula into B14.


-----
[1] https://app.box.com/s/axpnx7ua54tzppgft9ry

 

[tbarnett]

Hello joeu2004 and Mr Excel,

Another question/issue for you: can you advise how the formula would change if there is early access? In other words, interest expense begins earlier than the payment start date. Interest expense of course would increasing the liability and payments commence 3 months (or so) later. I tried to figure it out but continued to get circular references. There's also a couple other minor formulas I couldn't quite figure out how to calculate consistently with if functions. Pls note that I made a couple changes to your original (awesome) formula to fit into my existing schedule. Here's the file I'm working with, pls see the "lease model" tab:

https://linkedin.box.com/s/v6gqppsmwy6anfsv4317

Thanks again!!!

Best,
Tyler

 

[StanZA]

Great piece of work. What needs to change to cater for payments made at the beginning of the period? This solves payments at end of period.

 

[StanZA]

If you would like to avoid enumerating the IRR input in column D, you could replace the IRR formula in B8 with =varpmtRate(B2,B1,B6,B7). You can also delete the array-entered formula in B11. The VBA function varpmtRate is:

Function varpmtRate(npmt As Long, myPV As Double, _
    ByVal myPmt As Double, ByVal pmtIncr As Double) As Double
Dim i As Long
ReDim v(0 To npmt) As Double
myPmt = -Abs(myPmt)
pmtIncr = 1 + pmtIncr
v(0) = Abs(myPV)
v(1) = myPmt
For i = 2 To npmt
    If (i - 1) Mod 12 = 0 Then myPmt = myPmt * pmtIncr
    v(i) = myPmt
Next
varpmtRate = WorksheetFunction.IRR(v)
End Function

For comparison, download the updated file "varpmt int rate.xls" (click here) [1]. I left the IRR formula in B8, and I put the varpmtRate formula into B14.


-----
[1] https://app.box.com/s/axpnx7ua54tzppgft9ry

Hi Joeu2004,

Your solution here is perfect, but is designed for payments at the end of a period (not beginning or advance).

http://www.mrexcel.com/forum/excel-questions/761879-interest-rate-calculation-payment-increases.html

What needs to change to cater for payments made at the beginning of the period? Unfortunately I only use Excel and don't program in VB...or anything else.
Your help would be greatly appreciated.

 

[joeu2004]

ERRATA....

We can use the IRR function. Download "varpmt int rate.xls" (click here) [1]. It provides a 30-year template. The table below shows part of the content. It is explained below.

Your solution here is perfect, but is designed for payments at the end of a period (not beginning or advance).

StanZA's question is responded to in another thread that he started.

But as StanZA points out, the solution that I present here does not apply to capital lease models, which should have payments in advance.

As I explain in another tbarnett thread, I had focused on how Tyler said he calculated interest ("current balance * (imputed interest rate / 12)" and overlooked the fact that was a capital lease problem.

I have removed "varpmt int rate.xls" because it is misleading.

Download "varpmtRate.xls" instead (click here) [1]. It provides models for both payment in advance (e.g., capital leases) and payment in arrears (e.g., loans).


-----
[1] https://app.box.com/s/gncoio8ejkwjf25pcxb7