Interest rate calculation with payment increases - Payment beginning of period vs. Payment end of Period

[StanZA]

Hi All,

I would like to calculate the interest rate of a Capital Lease for a situation where the the rental/instalment amounts increases annually by a certain percentage over the term of the lease.

I do not want to draw up full tables to achieve this.

In the case of payments at the end of the period I have found a solution for this provided by joeu2004 under a similar posted under:
http://www.mrexcel.com/forum/excel-questions/761879-interest-rate-calculation-payment-increases.html

However I am unable (lack the knowledge/skills) to change this VBA function to accomodate payments in advance at the beginning of the period.

Please help.



For ease of reference the solution to the payments at the end of the period was as follows:

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 <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> function varpmtRate is:
Code:

Function varpmtRate(npmt As Long, myPV As Double, _ ByVal myPmt As Double, ByVal pmtIncr As Double) As DoubleDim i As LongReDim v(0 To npmt) As DoublemyPmt = -Abs(myPmt)pmtIncr = 1 + pmtIncrv(0) = Abs(myPV)v(1) = myPmtFor i = 2 To npmt If (i - 1) Mod 12 = 0 Then myPmt = myPmt * pmtIncr v(i) = myPmtNextvarpmtRate = WorksheetFunction.IRR(v)End Function</pre>
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.

 

[joeu2004]

I would like to calculate the interest rate of a Capital Lease for a situation where the the rental/instalment amounts increases annually by a certain percentage over the term of the lease.
[....]
In the case of payments at the end of the period I have found a solution for this provided by joeu2004 under a similar posted under: http://www.mrexcel.com/forum/excel-questions/761879-interest-rate-calculation-payment-increases.html [1]

However I am unable (lack the knowledge/skills) to change this VBA function to accomodate payments in advance at the beginning of the period.

My apologies, first, for not seeing your follow-up responses in the previous thread, and second, for the mistakes in "varpmt int rate.xls". Thanks for pointing out the latter.

(As I explained in a later tbarnett thread, I had focused on how Tyler said he calculated interest and overlooked the fact that this was a capital lease problem.)

The redesigned varpmtRate function below behaves more like RATE. Note that parameters have been changed to be more like RATE: varpmtRate(nper, pmt1, pmtPctIncr, pv, [fv], [type]). The defaults are also like RATE, namely: fv=0 and type=0 (in arrears).

Function varpmtRate(ByVal npmt As Long, ByVal myPmt As Double, _
    ByVal pmtIncr As Double, myPV As Double, _
    Optional myFV As Double = 0, _
    Optional ByVal pmtInAdv As Long = 0) As Double
    
    Dim i As Long
    
    myPmt = -Abs(myPmt)
    pmtIncr = 1 + pmtIncr
    If pmtInAdv <> 0 Then pmtInAdv = 1
    
    If pmtInAdv Then
        ' 1st pmt is part of 1st cash flow
        npmt = npmt - 1
        ReDim v(0 To npmt) As Double
        v(0) = Abs(myPV) + myPmt    ' subtract myPmt
    Else
        ' 1st pmt is 2nd cash flow
        ReDim v(0 To npmt) As Double
        v(0) = Abs(myPV)
    End If
    
    For i = 1 To npmt - 1
        v(i) = myPmt
        If (i + pmtInAdv) Mod 12 = 0 Then myPmt = myPmt * pmtIncr
    Next
    v(npmt) = myPmt - Abs(myFV)
    varpmtRate = WorksheetFunction.IRR(v)
End Function

The file "varpmtRate.xls" (click here) [2] demonstrates its use for both payment in advance and payment in arrears.

The simplified table below demonstrates that varpmtRate and the Excel IRR model return about the same results as Excel RATE for fixed payments both in advance and in arrears.

(In order to compare with Excel RATE, of course the payments must be fixed. The file "varpmtRate.xls" allows for payments that vary by a percentage every 12 months. It also includes a complete amortization schedule.)

[TABLE="class: grid, width: 617"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]Pmt In Advance
Cash Flows
[/TD]
[TD]
[/TD]
[TD="align: right"]Pmt In Arrears
Cash Flows[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Principal[/TD]
[TD="align: right"]$75,000,000.00[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]$75,000,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]1st Pmt[/TD]
[TD="align: right"]$600,000.00[/TD]
[TD="align: right"]$74,400,000.00[/TD]
[TD]
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]#Pmts[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]-$600,000.00[/TD]
[TD]
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD]End_Principal[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]-$600,000.00[/TD]
[TD]
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[TD]
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD]
[/TD]
[TD="align: right"]Lease Pmt In Advance
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[TD="align: right"]Loan Pmt in Arrears
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD]varpmtRate[/TD]
[TD="align: right"]0.203076972837080%[/TD]
[TD="align: right"]-$600,000.00[/TD]
[TD="align: right"]0.200131944514848%[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD]IRR
[/TD]
[TD="align: right"]0.203076972837080%[/TD]
[TD="align: right"]-$600,000.00[/TD]
[TD="align: right"]0.200131944514848%[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD]RATE
[/TD]
[TD="align: right"]0.203076972837116%[/TD]
[TD="align: right"]-$600,000.00[/TD]
[TD="align: right"]0.200131944514909%[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[TD]
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]12
[/TD]
[TD]Last CF row[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]-$600,000.00[/TD]
[TD]
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]::::
[/TD]
[TD]:::::::::::::
[/TD]
[TD="align: right"]:::::::::::::::::::::::::
[/TD]
[TD="align: right"]:::::::::::::::::[/TD]
[TD="align: right"]::::::::::::::::::::::::
[/TD]
[TD="align: right"]:::::::::::::::::[/TD]
[/TR]
[TR]
[TD="align: right"]146
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[TD]
[/TD]
[TD="align: right"]-$600,000.00[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
B8: =varpmtRate(B4,-B3,0%,B2,-B5,1)
B9: =IRR(C3:INDEX(C:C,B12))
B10: =RATE(B4,-B3,B2,-B5,1)
B12: {=MATCH(2,1/C1:C362)}
C3: =B2-B3
C4: =IF(ROWS($C$3:C4)>$B$4,"",-$B$3-IF(ROWS($C$3:C4)=$B$4,$B$5))

D8: =varpmtRate(B4,-B3,0%,B2,-B5)
D9: =IRR(E2:INDEX(E:E,B12))
D10: =RATE(B4,-B3,B2,-B5)
E2: =B2
E3: =IF(ROWS($E$3:E3)>$B$4,"",-$B$3-IF(ROWS($E$3:E3)=$B$4,$B$5))

C4 is copied down through C362, and E3 is copied down through E362.


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

[2] https://app.box.com/s/gncoio8ejkwjf25pcxb7