## Annuity – Excel Functions and Math

In August of 2014 I wrote a post called “Even Cash Flow Calculator” and published a spreadsheet along with it called “EvenMultCashFlows“. Read “Even Cash Flow Calculator” for more about the spreadsheet. I made some improvements to the spreadsheet, including the math equivalent for each Excel function I used, except IRR() which requires an iteration. The math includes formulas for when the payment is at the end of the period (referred to as annuity) or at the start of each period (referred to as annuity due). Simply check any box that says “Payment @ Start of Period (Annuity Due)” to change the from an annuity calculation to annuity due calculation. Yellow cells are input cells.

The Excel function math formulas that have both annuity and annuity due include PV(), FV(), NPER(), and PMT(). Since NPER(), for example, can require either present or future value and annuity or annuity due, there are four separate math formulas. Likewise for PMT().

In addition, I included  math formulas that solve for the Excel functions CUMIPMT() and CUMPRINC(). Also, there are formulas that will duplicate the information on a standard amortization schedule, by entering a single payment number.

Credit one formula to: http://mathforum.org/dr.math/faq/faq.interest.html

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.