Present and Future Value Proofs

Back in Oct. 2014, I posted a spreadsheet that calculated the present and future values of a cash flow with even periods and uneven cash flows. I thought I might expand on that spreadsheet by adding a few ways to proof the answers you get using the calculator. These are the formulas used for the present and future values: Proof1

The calculator:


1. The first proof is to use the Excel function IRR(). You need to rearrange the cash flow a little (the spreadsheet does that automatically) and enter the present value and future values in the yellow cells. This part of the spreadsheet was originally intended to calculate the APR of the cash flow given the PV or FV. If you enter the results of the calculator into the yellow cells and the IRR() formula results in the same APR you used for the calculation, the PV and FV calculations are correct. There are exceptions using the IRR() function as a proof if the cash flows are non-conventional. A non-conventional cash flow is a cash flow that has signs that change more than once. This means two or more negative and two or more positive signs. You could get multiple answers, depending upon how many times the signs change. Excel IRR() asks for a ‘Guess” so that it has a much better chance of coming back with the correct answer, if there is more than one possible answer.   Proof3 2. The PV and FV calculations of periodic cash flows are closely related to the familiar loan amortization schedule. In fact, you can use and amortization schedule to proof the calculator results. To proof the present value calculation, enter the same information into the amortization schedule as you did for the calculator, but the starting balance will equal the present value results  from the calculator. If the ending balance for the amortization schedule equals zero, the present value calculation is correct. Proof4   likewise, for proofing future value enter the same  information as above, but this time enter the starting balance as zero. If the ending balance of the amortization schedule equals the future value from the calculator, the future value calculation is correct. Proof5   3. Next, you could use the simple formulas for calculating the present and future values of a starting value and an ending value (only two cash flows). If one of the calculation is the correct PV or FV, than the other must be correct also. Proof6   Download “Proof” All spreadsheet written in Excel 2013           7767776hjhj

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *