Loan Amortisation

[apgmin]

I have a unique requirement, Suppose I loan to a friend $1000, He agrees to pay me back the same with say 9% interest in 10 years

Now the amortisation table for this is available for this freely on many sites. SO the monthly payment he needs to make to me is $12.67 for 120 months. Some also have features of additional payments

My problem is the repayment will be highly eratic:

My friend will pay me say $25 one month and then skip some monthly payment and then again pay me $10 say after 3 months and so on. SO instead of a blank extra payment column I need a column where I can note what he paid and when

Also the future monthly repayment amount should vary based on the amount he actually pays and when he actually pays and for the delayed payment he should be charged a penalty of 24%

 

[easy2understandexcel]

...He agrees to pay me back the same with say 9% interest in 10 years



My friend will pay me say $25 one month and then skip some monthly payment and then again pay me $10 say after 3 months and so on....

Get a new friend.
You can make a table like below:

[TABLE="class: grid, width: 1000, align: left"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Elapse time
[/TD]
[TD]Payment
[/TD]
[TD]Rate%
[/TD]
[TD]Rate$
[/TD]
[TD]Loan$
[/TD]
[/TR]
[TR]
[TD]1/1/2016
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]9%
[/TD]
[TD]-
[/TD]
[TD]$1,0000
[/TD]
[/TR]
[TR]
[TD]2/1/2016
[/TD]
[TD]=A3-A2
[/TD]
[TD]$25
[/TD]
[TD]=B3/365*$D$2
[/TD]
[TD]=D3*F2
[/TD]
[TD]=F2-C3+E3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[apgmin]

Get a new friend.
You can make a table like below:

[TABLE="class: grid, width: 1000, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Elapse time[/TD]
[TD]Payment[/TD]
[TD]Rate%[/TD]
[TD]Rate$[/TD]
[TD]Loan$[/TD]
[/TR]
[TR]
[TD]1/1/2016[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]9%[/TD]
[TD]-[/TD]
[TD]$1,0000[/TD]
[/TR]
[TR]
[TD]2/1/2016[/TD]
[TD]=A3-A2[/TD]
[TD]$25[/TD]
[TD]=B3/365*$D$2[/TD]
[TD]=D3*F2[/TD]
[TD]=F2-C3+E3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Something is wrong

even if the person makes the payment every month regularly he is penalised and the loan# increases instead of reducing

[TABLE="width: 432"]
<colgroup><col width="72" span="6" style="width:54pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 72"]Date[/TD]
[TD="class: xl63, width: 72"]Elapse time[/TD]
[TD="class: xl63, width: 72"]Payment[/TD]
[TD="class: xl63, width: 72"]Rate%[/TD]
[TD="class: xl63, width: 72"]Rate$[/TD]
[TD="class: xl63, width: 72"]Loan$[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 72, align: right"]1-Jan-16[/TD]
[TD="class: xl63, width: 72"]-[/TD]
[TD="class: xl63, width: 72"]-[/TD]
[TD="class: xl64, width: 72, align: right"]9%[/TD]
[TD="class: xl63, width: 72"]-[/TD]
[TD="class: xl65, width: 72, align: right"]$10,000 [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 72, align: right"]1-Feb-16[/TD]
[TD="class: xl63, width: 72, align: right"]31[/TD]
[TD="class: xl65, width: 72, align: right"]$25 [/TD]
[TD="class: xl63, width: 72, align: right"]0.007644[/TD]
[TD="class: xl63, width: 72, align: right"]76.43836[/TD]
[TD="class: xl65, width: 72, align: right"]$10,051 [/TD]
[/TR]
</tbody>[/TABLE]

 

[easy2understandexcel]

You put 10,000 in your example... one month of interest on a 10000 loan at 9% is more than the payment of $25...
As your own example shows...