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().

Even_All

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.

Download “EvenMultCashFlows

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

 

 

 

2 thoughts on “Annuity – Excel Functions and Math

  1. Pingback: The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions | Excel@CFO

  2. Pingback: Math for Excel Functions With Starting and Ending Values | Excel@CFO

Leave a Reply

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

WordPress spam blocked by CleanTalk.