• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Determining the Rate argument of the PMT function

D

DashboardNovice

Member
The formula to determine the amount of your payments on a debt is PMT(rate,nper,pv,fv,type)

Suppose you have a credit card debt. The debt is compounded daily and the payments are made monthly. Let's say the interest rate is given a 10%.

Based on this, what would you enter for the rate argument:

A. 10%
B. 10% / 12 (because payments are made monthly)
C. 10% / 365 (because debt is compounded daily)
 
Hi ,

I do not know anything about the function , but going through the Excel help on this , there are two points made :

Calculates the payment for a loan based on constant payments and a constant interest rate.
Click to expand...
Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.
Click to expand...
My reading of the above would be that since you are making payments monthly , even the rate should be calculated per month , after having been compounded daily.

Thus , using daily compounding , calculate the effective annual interest rate and divide that by 12 to get an effective monthly interest rate. Use this effective monthly interest rate for the rate parameter.

Narayan
 
Basically you have to convert the rate compounded daily to a rate compatible with your repayment period.

So here you must convert the 10% annual rate compounded daily to monthly rate, and then apply the new rate to the PMT formula.

If you 10,000 principal, 10% pa compounded daily, to convert to a monthly rate :

= FV(10%/360,30,,-10000) = 10083.67
Interest for one month = 10083.67 - 10000 = 83.67
Monthly Rate = (83.67/10000/30*360) = 10.040%
PMT = PMT(10.040%/12,12,-10000) = 879.35

Please be aware that this is assuming that every month has 30 days, thus the use of 360 days and not 365.

please feel free to correct, my math/finance is very rusty now
 
Last edited:
You must log in or register to reply here.
Back
Top