80444

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

  • filtering DocumentDb collection from Data Factory using unix-timestamp
  • Manipulating a dataframe within a Spark UDF
  • How do references in functions work?
  • How to update powerpivot pivot table filter via cell reference?
  • How to get file download speed (transfer rate) with php?
  • How to turn (A, B, C) into (AB, AC, BC) with Pig?
  • Accessing Rows In A LINQ Result Without A Foreach Loop?
  • .NET video play library which allows to change the playback rate?
  • jquery validation - waiting for remote check to complete
  • Django Haystack Rebuild Index
  • Does Apportable support to build library binary (.a/.so)?
  • How to retrieve information from antrun back to maven?
  • using System.Speech.Synthesis with Windows10 universal app (XAML-C#)
  • Function calls are not supported. Consider replacing the function or lambda with a reference to an e
  • How do I display a dialog that asks the user multi-choice questıon using tkInter?
  • Android Studio 1.3 RC3. Google Play services out of date. Requires 7571000 but found 6774470
  • Subclassing QGraphicsItem prevents me from being able to use itemAt() on a QGraphicsScene/View
  • Jenkins: FATAL: Could not initialize class hudson.util.ProcessTree$UnixReflection
  • Copy to all folders batch file?
  • Unable to install Git-core+svn by MacPorts
  • Alternative to overridePendingTransition() - Android
  • Why Encoding.ASCII != ASCIIEncoding.Default in C#?
  • one Local Olampyad Questions on Informatic in 2011
  • VSO Build — Response status code does not indicate success: 404 (Not Found)
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Installing Hadoop, Java Exception about illegal characters at index 7?
  • Convert array of 8 bytes to signed long in C++
  • R: gsub and capture
  • Proper folder structure for lots of source files
  • KeystoneJS: Relationships in Admin UI not updating
  • Benchmarking RAM performance - UWP and C#
  • -fvisibility=hidden not passed by compiler for Debug builds
  • Turn off referential integrity in Derby? is it possible?
  • Authorize attributes not working in MVC 4
  • Busy indicator not showing up in wpf window [duplicate]
  • Reading document lines to the user (python)
  • Binding checkboxes to object values in AngularJs
  • Python/Django TangoWithDjango Models and Databases
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?