interest due on capital balance
I have lent money for a period of 36 months where the borrower pays 1/36th of the original loan back each month together with interest, at the monthly rate, on the remaining loan balance.
I would like to have a column in my spread sheet which shows the interest due at the end of the current month and which automatically shows the reduced interest due as time goes on.
For example, for a loan of £100 at 10%/year interest, taken out on November 1st, would, during November, the cell would show that on December 1st interest of (£100 x 10%)/12 = £0.83 is due. Come December 1st and until December 31st the interest would be shown as (£90 x 10%0/12 = £0.75 and so on.
Is there a formula which would return this variable interest?