Macaulay Duration of an Amortizing Loan

The term “duration” is often used interchangeably by market participants to denote either a volatility measurement, a weighted term to maturity, or a portfolio risk measurement. In practice there is more than one definition of duration. The following describes the most basic duration along with it’s calculations.

In a future posts we will discuss Modified Duration, Effective Duration, and Convexity.


The concept of duration was developed in 1938 by Frederick Macaulay. Duration was an effort to more accurately measure the term of an option-free fixed income security. Maturity alone did not take into consideration all of the cash flows (i.e. coupon payments). Macaulay reasoned that if the present value of each cash flow was weighted by the time it takes to receive that cash flow, the resulting weighted average time period would be a more realistic measurement of term than maturity. Macaulay duration can be thought of in terms of the average length of time a fixed income investment is outstanding.

The math (U.S. bonds typically pay semiannually):

Excel has a Macaulay Duration function, but it is designed only for a fixed rate, non-callable, security that pays all principle at maturity.

Above in green, you see a formula to calculate Macaulay Duration. Now that we have a formula , we can make some simple adjustments to use it on a loan or pool of loans, that may have a CPR and servicing, if applicable. I am using the same amortization schedule used in the servicing post back in August. I assume we own a slightly seasoned pool of loans that we originated. They all have an APR of 5.0%, an average life of 331 months, and since we originated the loans, no servicing.


Macaulay duration of an amortized loan:

The SUMPRODUCT function calculates the present value factor at each month (using the market rate). It then multiplies the factor times the cash flows  (column K is a helper column of cash flows to owner), and then weights that product by time (column I). Then the SUMPRODUCT results are divided by the present value of all cash flows to the owner, discounted by the market rate, resulting in a duration of 5.509. I stuck in another formula to calculate the loans Half Life. Half Life is the month when half of the original loan balance is reached. Formula =(MATCH((D4/2,B19:B500,-1)/12.

Download workbook “MacaulyDuration” from: 

Downloads Written in Excel 2013

3 thoughts on “Macaulay Duration of an Amortizing Loan

  1. Pingback: Effective Duration, Convexity, and Convexity Adjustment For Loans | Excel@CFO

Leave a Reply

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

WordPress spam blocked by CleanTalk.