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?

Thanks in advance.

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

Answer1:

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">

Answer2:

You can use an annuity formula

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

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

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Net Present Value in Excel for Grouped Recurring CF