Finding net future value for periodic cash flows in uniform amount may be possible in Excel yet if you wanted to find NFV for periodic cash flows that are in uneven amounts then you are out of luck. But you worry not, I will show you how easy it gets to find net future value when cash flows are not in constant amount by using a simple Excel NFV function that is provided by 3rd party add-ons. The following section begins by defining net future value and the different types of interest compounding. Once you have grasped the concept of net future value, I will move on to show you how tadNFV Excel function makes it possible to find net future worth.
What is NFV
To explain NFV, I will take an example of savings account at a bank that promises to pay you interest on your deposits. The banks in the US will usually advertise an APR or AEY, the acronyms stand for annual percentage rate and annual effective yield. This is the rate at which the bank promises to make you interest payments on your deposits. If a bank were to state that it pays 6% annual interest compounded monthly then that means at the end of each month, your money in the account will appreciate by a rate of 6%/12 or 0.5%. The interest earned this way is called compound interest as compared to simple interest where your ending balance at terminal date accrues interest. With compound interest not only your deposited amount will earn you money but also the interest earned in previous periods will appreciate at the interest rate. And NFV or net future value is the measure that finds the final or terminal worth of series of deposits or payments that earn compound interest.
Discrete vs Continuous compounding
But things are more complicated with compound interest as the element of compounding periods get included in the picture. In the example I referred to previously, the bank stated interest was to be compounded monthly. However, most banks in the US pay and charge interest on ever small fraction of time thus making it possible for money to earn interest on infinite scale of time. This sort of compounding of interest is also referred to as continuous. And one earns more of interest with continuous compounding as compared to periodic or discrete compounding.
How to use tadNFV Excel function
To find net future value of periodic cash flows that are in uneven amounts, the Excel tadNFV function may be used. This is a 3rd party user defined Excel function that is part of a large library of financial functions called TADXL. tadNFV function calculates net future value when you enter the series of cash flows as either a range of cells or money amounts enclosed in curly brackets each separated by a comma. The interest rate is one of the other values that you enter either as a decimal value of 0.05 or as a percentage such as 5%. There are couple of other optional values that you may enter with this functions; one of which is called type that accepts a value of 1 or omitted for start of period deposits and a value of 0 for end of period deposits. The last value for an argument called compounding permits you to select discrete compounding with a value of 0 or omitted and value of 1 for continous compounding. The following example shows you the use of tadNFV function in Excel to find net future value of series of payments and deposits.
| A | B | C | D | E | F | |
| 1 | Cash Flow | -10000 | 3000 | 3000 | 3000 | 3000 |
| 2 | NFV | =tadNFV(2%, B1:F1, 0, 0) | $1,540.50 | =tadNFV(2%, B1:F1, 0, 1) | $1,535.68 | |
| 3 | NFV | =tadNFV(2%, B1:F1, 1, 0) | $1,571.31 | =tadNFV(2%, B1:F1, 1, 1) | $1,566.70 |
Excel net future value calculator
You can download this Excel template to find net future value by using tadNFV Excel function.
