Excel formula for amortization schedulefor a loan with daily compound interest
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
(1)1 Comments
Sorted by latest first Latest Oldest Best
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
Terms of Use Privacy policy Contact About Cancellation policy © freshhoot.com2025 All Rights reserved.