 # Net Present Value in Excel for Grouped Recurring CF

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?

<img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/wXsOq.png" alt="enter image description here">

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:

```=myNPV(C20/C19,B3:B17,C3:C17)+B2 ```

This has three criteria: The rate per, amounts range, and corresponding number of times.

<img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/n1kws.png" alt="enter image description here">

In `D1` use `=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