Present Value For Amortizations With Dates

Excel’s functions for present and future value of cash flows with dates, will not work with the present and futureĀ  value of an amortizing loan with dates based on an actual/360 basis.

A few years back, a young man contacted be about calculating the present value of an investment made at his firm. He had just taken over for the previous person in charge of investments and was having trouble calculating the present value of one of the short-term investments. The cash flows looked like this:

He knew that the interest rate was .67% and the notional amount paid for the investment was $181,600,000. Using the Excel formula for present value (XNPV) he calculated the present value to be $181,607,100.75. Assuming the payments were based on LIBOR (Actual/360), we calculated the present value to be $181,601,298.47. It turned out that the investment was based on an actual/360 basis, but was a fixed rate amortizing investment, based on LIBOR, which is an actual/360 basis.

I thought readers might be interested in how to calculate the present value of an amortizing investment with dates, either an amortizing loan or a fixed rate amortizing swap based on LIBOR. I made the day count and base year variable. The day count can be Actual or a 30 day month. The base year can be 360 or 365.

The present value discount factor formula is:

The present value is then the sum of the discount rates times the cash flows.

I also included the future value of the cash flows on the same basis.

Then I created a amortization schedule as a proof that the present value calculation will amortize the cash flows to a zero balance.

The reason the spreadsheet is .xlsm (contains macros), is because I also calculated the same present and future values with UDF in VBA.

 

 

Download PVA.xlsm

 

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.

2 Comments

  1. Hi Don, i would like to contact you, but i couldnt find any telephone or email. I would like to have a quick chat with you about some of your models as well as some credit score model.
    Thanks,
    Henrique

Leave a Reply

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