Home |
Search |
Today's Posts |
#3
|
|||
|
|||
" wrote: amalecki wrote... I am using Excel 2002 I am trying to calculate a fixed monthly payment on a 24 month loan. The problem is the bank uses a 360 day basis when they calculate the fixed monthly payment. Excel's PMT formula has a 365 day basis. .... If you have 24 identical monthly payments, the only trick is in calculating the *effective* monthly interest rate. That is, whether you use 360, 365 or 366 day years, there are always 12 months in a year. Your effective interest rate is the rate used for compounding, but banks like to quote *nominal* interest rates which are lower than annualized effective interest rates. (Truth in lending?!) Anyway, if your bank quotes nominal interest rates for daily compounding, then what I suspect is that the bank calculates the monthly effective interest rate as (1 + Nominal Rate / 360)^30 - 1 rather than as (1 + Nominal Rate / 365)^(365/12) - 1 For a 6.0% nominal interest rate, the former returns an effective monthly interest rate of 0.5012102% (so an annual effective rate of 6.18312%) while the latter gives 0.5012108% monthly (6.18313% annually). What's the stated interest rate and the ratio of your monthly payment to the loan amount? amalecki writes: The only interest rate I have from the Bank is 6%; the monthly payment the bank calculated is $9,465.67, based upon a loan amount of $213,402.24. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate EDATE using days not months? | Excel Discussion (Misc queries) | |||
Does Excel 2000 have a 'datedif' function to calculate the number. | Excel Worksheet Functions | |||
How do I use the IF function to calculate date | Excel Discussion (Misc queries) | |||
Calculate # of Days from one date to another | Excel Worksheet Functions | |||
how do I make a function to count days? | Excel Worksheet Functions |