Calculate a PMT amount when there is an odd first period
Oct
18
Written by:
Charles Flock
10/18/2011 8:08 PM
11 Financial Calculations that you can’t do in EXCEL - Part 11 of 11
11. Calculate a PMT amount when there is an odd first period.
This is a very common problem. The EXCEL time-value-of money functions all assume that each period is the same length. But this isn’t how the real world operates. You might buy something on Ocober 15th, and the first payment isn’t due until the first of December or even the first of January, and then you will make monthly payments. How can you calculate the payment amount?
In EXCEL, the short answer is that you cannot. Which is really surprising, as this is not a very complicated calculation. Here’s one example:
SELECT wct.LPMT(
36000 --PV
,'2011-10-15' --Loan Date
,.059 --Rate
,'2012-01-01' --First Pay Date
,36 --Number of Payments
,12 --Payments per year
,360 --Days in year
,0 --FV
,'A' --Interest Rule
) as PMT
This produces the following result.
PMT
----------------------
1101.76321093092
We can double check that this is correct payment amount with the following SQL Server statement:
SELECT *
FROM wct.AMORTSCHED(
36000 --PV
,'2011-10-15' --Loan Date
,.059 --Rate
,'2012-01-01' --First Pay Date
,36 --Number of Payments
,12 --Payments per year
,360 --Days in year
,0 --FV
,'A' --Interest Rule
)
This produces the following result, which I have put into table to make it easier to read.
As you can see, even though the first period is longer than all the other periods, we have calculated a payment amount that fully recognizes all the interest and fully amortizes the principal. It’s simple enough to do this, but there is no function that lets you do it in EXCEL.