Home » Excel Spreadsheet » Relationships – XIRR(), Amortization, Present & Future Value

Why do I seem obsessed with amortization schedules?  I  think they make it easier to understand other calculations. In this spreadsheet we will consider the relationship between the Excel XIRR() function (Internal Rate of Return), Present and Future Values, and of course an amortization schedule.

The first thing to discuss is the difference between APR (Annual Percentage Rate) and APY (Annual Percentage Yield). I don’t mean the regulatory requirement APY for banks with points and other fees added in (although they are related). I have gotten into the habit of calling compounding that occurs only once a year APY and compounding that occurs at equal  periods during the year (i.e.. monthly, quarterly, daily, etc.), APR. You will find the distinction throughout all my spreadsheets.

This spreadsheet is concerned with XIRR(). XIRR returns an annual rate of return (APY) while IRR() returns a periodic return that when multiplied by the number of compounding periods in a year, returns an APR.

The results for an IRR calculation (multiplied by the number of periods in a year) fits nicely into a regular loan amortization schedule (see post Present and Future Value Proofs), while the results from XIRR do not. Even if you used the conversion formula (see post  Simple Formula for Converting Compound Interest Rates) to convert from one compound rate to an annual rate, it would probably not match exactly. That is because XIRR() assumes an actual day count and a 365 day year, where a normal loan amortization schedule (in the U.S. anyway) assumes a 30 day month and a 360 day year. A slight change in the way interest is calculated will amortized the XIRR cash flow. Since the XIRR() results in an annual yield (APY) with a 365 day year, interest must be calculated on an annual and 365 day basis:

XIRR5

 

XIRR1XIRR2

XIRR4

XIRR3

Download “XIRR

 

 

Leave a Reply

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

*
*