2

Can anyone help me. I'm after an amortization schedule for loans that has a daily compound interest added with a monthly repayment. I've calculated the monthly repayment but I can't find an excel formula that provides me with a breakdown of principal and interest on these monthly payments. I've tried everything to create my own but I can create one for monthly compound but not for daily. Many thanks


Thanks for replying - is it therefore impossible to use the ppmt function in excel using a daily compound rate? The formula is PPMT(rate, per, nper, pv, [fv], [type]) so for the rate I put in 8.5%/365. This worked for monthly i.e. 8.5%/12 but gives me different amounts for principal and interest (ipmt i did the same for) as to what I know the monthly payment is when trying to calculate using a daily compound formula. As you can probably tell, I'm not a whizz on Excel so unsure of how I can utilise this PPMT function with the formula you have given me? Any further advice would be much appreciated

  • Karen - welcome to Money.SE. Note, answers should be just that, a new answer. Clarification to your question should be edits. Responses to answers can be comments to the answer, but this ran long, so I put it in your question. – JoeTaxpayer Aug 18 '15 at 16:32
1

Just convert the "daily compound interest" into "effective monthly interest".

If Nominal Interest is 8.5% per per annum compounded daily,

each day is 8.5% / 360, so each month is ((1 + (8.5% / 360))^30) - 1.

So the effective interest over 1 month is ((1 + (8.5% / 360))^30) - 1,

and the nominal interest per annum is ((((1 + (8.5% / 360))^30) - 1) * 12) compounded monthly.

Edit: You can just put ((1 + (8.5% / 360))^30) - 1 = 0.0071076374384 into rate of PPMT or IPMT

  • For daily interest, the need to calculate exact value for X days, don't you need to divide by 365? – JoeTaxpayer Aug 18 '15 at 16:17
  • @JoeTaxpayer money.stackexchange.com/questions/15941/… . Although it is not the only way, it is the most popular way for monthly payments. en.wikipedia.org/wiki/Day_count_convention – base64 Aug 18 '15 at 16:27
  • Got it. Your way works for my mortgage. I pay between the 20th and the next 15th and the payment is treated the same. If some loan credits an account differently with a payment on the 3rd vs the 2nd, the 360 might not be exact. If not, I agree with your approach. – JoeTaxpayer Aug 18 '15 at 16:30
  • Many thanks - I've now input this in and it works. your help and advice is appreciated – Karen Aug 18 '15 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.