Loan Amortisation

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
139
Office Version
  1. 2013
Platform
  1. Windows
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%
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
...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:

Date
Elapse time
Payment
Rate%
Rate$
Loan$
1/1/2016
-
-
9%
-
$1,0000
2/1/2016
=A3-A2
$25
=B3/365*$D$2
=D3*F2
=F2-C3+E3

<tbody>
</tbody>
 
Upvote 0
Get a new friend. :)
You can make a table like below:

DateElapse timePaymentRate%Rate$Loan$
1/1/2016--9%-$1,0000
2/1/2016=A3-A2$25=B3/365*$D$2=D3*F2=F2-C3+E3

<tbody>
</tbody>







Something is wrong

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

DateElapse timePaymentRate%Rate$Loan$
1-Jan-16--9%-$10,000
1-Feb-1631$25 0.00764476.43836$10,051

<colgroup><col width="72" span="6" style="width:54pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top