Below is a cash flow table for 60 periods.
There is a set of recurring cash flows. Is there a simple way in excel to calculate the NPV for all 60 periods (monthly cashflows) without have to create a table of 60 rows and using the NPV formula?
So the formula for 60 line items would be something like this:
=NPV(PERIODIC RATE, VALUES OF CF 1 - 60) + CF0
But can you shortcut it if you know there are recurring cashflows in excel and not have to enumerate all 60 rows?
Thanks in advance.
There is no built in function that will do this, but we can build our own. This is a UDF(User Defined Function):
Function myNPV(rate As Double, vl As Range, times As Range) If vl.Cells.Count <> times.Cells.Count Then Exit Function Dim vlArr() As Variant Dim timesArr() As Variant Dim ovlArr() As Double Dim i&, j&, t&, cnt& vlArr = vl.Value timesArr = times.Value For i = LBound(vlArr, 1) To UBound(vlArr, 1) If vlArr(i, 1) <> "" Then t = t + timesArr(i, 1) End If Next i cnt = 1 ReDim ovlArr(1 To t) For i = LBound(vlArr, 1) To UBound(vlArr, 1) If vlArr(i, 1) <> "" Then For j = 1 To timesArr(i, 1) ovlArr(cnt) = vlArr(i, 1) cnt = cnt + 1 Next j End If Next i myNPV = Application.WorksheetFunction.NPV(rate, ovlArr) End Function
In the workbook hit Alt-F11. This will open the VBE.
Go to Insert and insert a module. This will open a new module. Copy and paste the code above.
The you can call it like any other formula:
This has three criteria: The rate per, amounts range, and corresponding number of times.
You can use an annuity formula
=1/($B$7/12)*(1-1/(1+$B$7/12)^C1)*B1*1/(1+$B$7/12)^(SUM($C$1:C1)-12) and copy down