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

Improve this question
1
  • 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.
    – JTP - Apologise to Monica ♦
    Commented Aug 18, 2015 at 16:32

1 Answer 1

Reset to default
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

Improve this answer
4
  • For daily interest, the need to calculate exact value for X days, don't you need to divide by 365?
    – JTP - Apologise to Monica ♦
    Commented Aug 18, 2015 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
    Commented Aug 18, 2015 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.
    – JTP - Apologise to Monica ♦
    Commented Aug 18, 2015 at 16:30
  • Many thanks - I've now input this in and it works. your help and advice is appreciated
    – Karen
    Commented Aug 18, 2015 at 16:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .