## 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.

 Previous: Calculate the price of a bond with an odd last coupon before the last coupon period

Tags:
Categories:

## Search Blogs

 KeywordsPhrase

## Blog Archives

Archive
 < September 2024 >
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345
Monthly
Go