more-arw search

Q&A Forum

I need a refresher in computing the present value of a series of monthly payments.

Calculate net present value of future cash flowsCan you refer me to a reliable website that might provide a tutorial? Also, I need a present value table that will enable me to do this calculation for a term of 35 years or more.


Topic Expert
Lee Andrews
Title: P/T CFO, Business Consultant
Company: Pacific Bag, Inc./Other Clients
(P/T CFO, Business Consultant, Pacific Bag, Inc./Other Clients) |

You don't need a website or a table. Good 'ol Excel and a simple formula will do it. I was not sure if you meant you need grass roots training in the basics of it all, or if you meant is there a tool to do it for me. I always write mine in Excel -- e.g. in assessing capital lease accounting. Your biggest challenge question will be what discount rate to use. I can send you a very easy template by email if you like, but the formula (for each year or period) is PV=PMT/[(1+i)^n] where PV is today's present value, PMT is the amount received later in year/period "n", and "i" is the assumed year/period interest rate for discounting purposes. Just do that for each year/period (whether 5 or 35, no matter) in columnar form, add the PV column up and you have your total PV. Excel has formulas which do all this anyway, and more sophisticated ones where you have equal payment amounts over time, etc. Anything more complex than that and the answer may get a bit longer. If you do need a tutorial -- try Wikipedia and there are several videos on YouTube.

David Collins
Title: CEO
Company: Glentyde Capital Advisors
(CEO, Glentyde Capital Advisors) |

Just piling on with my 2 ¢; I think Lee's got it well covered. All of the popular spreadsheets (e.g., Calc, Excel, Gnumeric) have PV functions built in.

If you need the PV of a series of level payments---such as you'd have with a typical mortgage loan---you can call the spreadsheet's PV function and give it the three necessary arguments (discount rate, amount of each periodic payment, total number of payments), and it'll return the stream's PV.

There's also a separate built-in function for a scenario involving a sequence of cash flows which vary in amount. See the spreadsheet's help docs for more details.

PV tables have really been rendered obsolete by the foregoing. With preprinted tables you'll have to interpolate your answer unless your particular situation just happens to exactly match one of the rate / term combos provided on the table. With a spreadsheet function you get a precise answer for any given set of facts; no interpolation required.

Back to the situation it sounded like you're specifically asking about---a series of (constant) payments over some number of periods: if you wanna rock it old school with a pocket calculator, there's a simple closed-form formula for the PV:

P * [1 - (1 + r)^(-n)] / r

where P, r, and n are, respectively, the periodic payment amount, the discount rate, and the number of payments. Remember to express r as a monthly rate if the payments are being made monthly, and n should thus be the number of months.

With that formula you can whip out the PV of any constant-payment mortgage, for any number of periods, using nothing more than a $7 pocket calculator and 30 seconds of your time (although with the number of keystrokes reduced considerably if you're using a calculator which can handle exponentiation).

Jill Nickerson
Title: CFO
Company: In Transition
(CFO, In Transition) |

A follow on to Dave's formula. The following is how the formula would look in Excel. Assuming the interest rate is 5% and year 0 is in A1, year 1 in A2...


I tend to use the formula more than the "PV" function.

Robert Ewalt
Title: Exam Development Manager
Company: Institute of Certified Management Accoun..
(Exam Development Manager, Institute of Certified Management Accountants) |

Also, one could use a financial calculator, such as Texa Instruments BAII Plus. Most calculators come from the factory set to monthly compounding, which I usually cahnge to annual. With the default setting, put in the full annual discount rate of I/Y.


Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email [email protected] to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.