UDF for NPV with an escalation each year

[GDH]

Hi all

I have a the following data for an NPV calculation:

Start date - Start date of lease
End date - End date of lease
Rate - discount rate
n - number of months of the lease
pmt - initial payment (to be escalated by escalation rate at each escalation interval)
Escalation rate (eg. 8%)
Escalation interval (eg. every 12 months)

I am trying to do an NPV which takes into account the escalation at each interval without having to draw out a cashflow table (cos I have 150 of these leases for which I require the NPV).

I have thought of using XNPV and trying to use arrays within but I can't get it right.

In my mind I can see a UDF looking something like this

=EscalationNPV(rate, pmt, escalation rate, escalation interval, n)

Is this possible in a UDF?

Thanks

 

[joeu2004]

I have a the following data for an NPV calculation:
Start date - Start date of lease
End date - End date of lease
Rate - discount rate
n - number of months of the lease
pmt - initial payment (to be escalated by escalation rate at each escalation interval)
Escalation rate (eg. 8%)
Escalation interval (eg. every 12 months)
[....]
Is this possible in a UDF?

Perhaps the following meets yours need. I highlighted some additional parameters that you did not consider. See the explanation below.

Function myNPV(discntRate As Double, n As Long, ByVal myPmt As Double, _
        incRate As Double, incFreq As Long, myPV As Double, _
        Optional myType As Long = 0) As Double
    ' myPmt and myPV are both positive, unlike most Excel functions.
    ' discntRate is period rate, not annual rate
    Dim i As Long, k As Long, discnt As Double
    If myType = 0 Then
        ' pmt at end of period, like a loan
        myNPV = -myPV
        k = 1
    Else
        ' pmt at beginning of period, like a lease
        myNPV = -(myPV - myPmt)
        k = 2
    End If
    discnt = 1
    For i = k To n
        If k > incFreq Then
            myPmt = myPmt * (1 + incRate)
            k = 1
        End If
        discnt = discnt * (1 + discntRate)
        myNPV = myNPV + myPmt / discnt
        k = k + 1
    Next
End Function

The key change is the addition of the present value of the asset (myPV). That is needed in order to calculate the NPV.

Another key change is the myType parameter, which indicates when payments occur each period: 0 for the end and non-0 for the beginning, like most Excel financial functions.

Typically, payments for leases (your situation) are at the beginning. So myType should be 1. Consider the following example:

	A	B
1	PV	100,000.00
2	discnt rate	3.00%
3	#pmts	66
4	init pmt	3,000.00
5	%pmt incr	8.00%
6	pmt incr freq	12
7	type	1

<tbody>
</tbody>

=myNPV(B2,B3,B4,B5,B6,B1,B7) in H1 returns about -624.84.

Note that discnt rate (B2) is the periodic discount rate, not the annual rate. So in your case, it is the monthly discount rate.

Also note that the NPV is the present value of the remaining balance, assuming discount rate is the periodic interest rate. So the remaining balance can be calculated by:

=-H1*(1+B2)^B3

For this example, the remaining balance is about 4,395.64.

Finally, if init pmt is less than PV*discntRate, the balance negatively amortizes, at least for some periods. That is, the periodic ending balance is more than PV (B1), the initial balance.

And using Goal Seek or Solver, we can determine the break-even init pmt that results in a specified remaining balance (FV), typically zero. If the actual init pmt is more than that amount, the remaining balance is negative.

Let me know if you are interested in how to calculate the break-even init pmt.

 

[GDH]

Thanks for the replyI guess NPV was the incorrect term - its actually just a regular PV (of the future lease payments). The reason I used the term NPV is because the PV function in Excel requires consistent payments - and it is the XNPV function that I would use for the series of cashflows - even though I actually don't have an initial outflow. So I can just leave the PV argument as 0 I imagine? I will play around with this - thank you again for the assistance!

 

[joeu2004]

I guess NPV was the incorrect term - its actually just a regular PV (of the future lease payments). [....] So I can just leave the PV argument as 0 I imagine?

Yes. If myPV is zero, myNPV returns the present value of the schedule of payments, assuming discntRate is the periodic (monthly) interest rate and the remaining value (FV) is zero.

If you want the (N)PV that leaves a specified remaining balance, subtract the PV of the intended remaining balance from the myNPV result. The PV of the intended remaining balance (1000, for example) is:

=1000/(1+B2)^B3

And that makes more sense as an objective. I could not quite understand why you wanted what I thought (incorrectly) that you asked for.

 

[joeu2004]

Errata....

Yes. If myPV is zero, myNPV returns the present value of the schedule of payments, assuming discntRate is the periodic (monthly) interest rate and the remaining value (FV) is zero.

If you want the (N)PV that leaves a specified remaining balance, subtract the PV of the intended remaining balance from the myNPV result. The PV of the intended remaining balance (1000, for example) is:
=1000/(1+B2)^B3

Correction: we would add the (positive) PV of the remaining balance in this case.

Anyway, now that I understand what you really want, the VBA function below (note the name change) does all the arithmetic, including accounting for non-zero FV. Usage based on my previous example:

=myPV(B2,B3,B4,B5,B6,0,B7)

The zero is the intended remaining balance (FV).

Function myPV(discntRate As Double, n As Long, ByVal myPmt As Double, _
        incRate As Double, incFreq As Long, Optional myFV As Double = 0, _
        Optional myType As Long = 0) As Double
    ' myPmt, myFV and myPV are all positive, unlike Excel financial functions.
    ' discntRate is periodic rate, not annual rate
    Dim i As Long, k As Long, discnt As Double
    If myType = 0 Then
        ' pmt at end of period, like a loan
        myPV = 0
        k = 1
    Else
        ' pmt at beginning of period, like a lease
        myPV = myPmt
        k = 2
    End If
    discnt = 1
    For i = k To n
        If k > incFreq Then
            myPmt = myPmt * (1 + incRate)
            k = 1
        End If
        discnt = discnt * (1 + discntRate)
        myPV = myPV + myPmt / discnt
        k = k + 1
    Next
    myPV = myPV + myFV / discnt
End Function