Effective Duration – No VBA or Amortization

Back in October I showed how to calculate effective duration and convexity on a pool of fixed rate mortgages with the help of amortization schedules. In November I posted a formula that could calculate the price of a fixed rate pool of mortgages, including the use of CPR and servicing, in one cell. That same month I had another post showing how a mortgage pool pricing table and an ALM interest rate shock analysis could be done, using the one cell formula.

I thought it might be fun to show how to calculate effective duration and convexity using the one cell formula for price:


Download “EffectiveDurationOneCell

All spreadsheets created with Excel 2013




Don Pistulka
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.


  1. Hi, I have a finance question? We sell manufactured housing loans. There is a premium involved that sits in a deposit account with the bank or credit union as protection against credit losses and prepayments.

    We get to the premium in a weird way, in that it is a dollar amount. We are constantly asked about the effective yield on the product.

    One larger bank voiced a concern that we were not calculating the effective yield correctly. They said the principle amount of the loan plus the premium should be the denominator (I agree), and they said the numerator should be the interest minus the amortization of the premium? By doing this, aren’t we double counting the impact of the premium?

    Interested in hearing your take? An example: A 70,000 loan at a rate of 7%, sold with a premium of $14,685.15. Term is 20 years. Assume it goes to term, what would you calculate the effective yield to be?

    1. Robert,
      I am not familiar with the practice of leaving a deposit on a loan, but with the following assumptions I get an effective yield on your example of 5.38%.

      1. Deposit stays constant for the term of the loan
      2. You get the deposit back at the end of the loan
      Let me know if I am missing something.
      I will send you a spreadsheet.

Leave a Reply

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