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.