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