Hi everyone!
I am calculating simple interest payments for a particular month. I have the outstanding principal and the rates can change within a month. So far, I have used the formula below:
=IF(E8="-",I8*(M8-1)/365*P8%,I8*(DAY(E8)-1)/365*D8%+I8*(M8-DAY(E8)+1)/365*P8%)
E column stores the date on which the rate changed and also thus denotes whether it has changed or not.
I=Outstanding Principal
D column store the old rate
P column stores the new rate
M stores the number of days in that month
Now, I also want to incorporate repayments in the calculation. But, I cant quite figure out how to do it because a repayment can happen before or after a rate change!
Any help would be appreciated.
I am calculating simple interest payments for a particular month. I have the outstanding principal and the rates can change within a month. So far, I have used the formula below:
=IF(E8="-",I8*(M8-1)/365*P8%,I8*(DAY(E8)-1)/365*D8%+I8*(M8-DAY(E8)+1)/365*P8%)
E column stores the date on which the rate changed and also thus denotes whether it has changed or not.
I=Outstanding Principal
D column store the old rate
P column stores the new rate
M stores the number of days in that month
Now, I also want to incorporate repayments in the calculation. But, I cant quite figure out how to do it because a repayment can happen before or after a rate change!
Any help would be appreciated.
