Question 1196137: Please help with the homework:
Draw up an amortization schedule for a loan of R5000 that is repaid in annual payments over five years at an interest rate of 15% per annum.
Answer by math_tutor2020(3817)   (Show Source):
You can put this solution on YOUR website!
We first need to find the annual payment.
P = unknown annual payment
L = 5000 = loan amount
r = 0.15 = decimal form of the annual interest rate
n = 5 = number of years
 approximately
 approximately
The annual payment is R1491.58
You can use the PMT function in your favorite spreadsheet software to determine this payment quickly.
While it is optional to use spreadsheet software to determine the payment, I strongly recommend using spreadsheet software for the remaining parts of the problem.
I'm using LibreOffice to get the job done. It is free software. There are many other free alternatives out there.
If you already have Excel, then stick to that.
Let's have a table with the following column labels:- Year
- Starting Balance
- Interest Payment
- Principal Payment
- Ending Balance
Each column label should be self-explanatory.
We'll have the values 1,2,3,4,5 in the "year" column
Write 5000 in the starting balance of year 1.
The interest rate is 15% per annum, ie it is the annual rate
15% of 5000 = 0.15*5000 = 750
This means R750 is the interest payment for year 1, so 1491.58 - 750 = 741.58 is the principal for this year.
Subtract the principal from the starting balance
5000 - 741.58 = 4258.42
The balance of R5000 drops to R4258.42
I.e. this is the ending balance of year 1
Here's what the table looks like so far
| Year | Starting Balance | Interest Payment | Principal | Ending Balance | | 1 | 5000 | 750 | 741.58 | 4258.42 |
Of course we've only done year 1
Copy the ending balance of year 1, and paste it into year 2's starting balance. You can use cell references to avoid copy/paste. Cell references allow for the table to scale much easier.
Then carry out the same steps as mentioned earlier
- 1) Compute the interest payment. Take 15% of the starting balance for that year (this means the interest payment will decrease over time)
- 2) Subtract the interest from the payment 1491.58 to determine the principal
- 3) Subtract the principal from the starting balance for that given year; this yields the ending balance for that year
This is what we get if we carry out the process for all five years
| Year | Starting Balance | Interest Payment | Principal | Ending Balance | | 1 | 5000 | 750 | 741.58 | 4258.42 | | 2 | 4258.42 | 638.76 | 852.82 | 3405.60 | | 3 | 3405.60 | 510.84 | 980.74 | 2424.86 | | 4 | 2424.86 | 363.73 | 1127.85 | 1297.01 | | 5 | 1297.01 | 194.55 | 1297.03 | -0.02 |
Frustratingly, we don't end with a final balance of 0
Instead we have a -0.02; meaning we should adjust the final payment of 1491.58 to be 1491.56 instead. You'd save R0.02 here.
So this is what it looks like with that final correction
| Year | Starting Balance | Interest Payment | Principal | Ending Balance | Notes | | 1 | 5000 | 750 | 741.58 | 4258.42 | | | 2 | 4258.42 | 638.76 | 852.82 | 3405.60 | | | 3 | 3405.60 | 510.84 | 980.74 | 2424.86 | | | 4 | 2424.86 | 363.73 | 1127.85 | 1297.01 | | | 5 | 1297.01 | 194.55 | 1297.01 | 0 | Payment is R1491.56 instead of R1491.58 |
This is the fully completed amortization table for all five years. It shows what the balance is at any given snapshot of time, and it shows how much is paid in interest and principal.
With the exception of year 5, the other prior years have a payment of R1491.58
Year 5's payment is R1491.56
Amortization tables are commonplace in loans, to the point that many financial websites and spreadsheet software packages automatically generate them. This is handy for those who don't have much background in mathematics. You can use those tools to check your work. It may be tempting to rely entirely on those tools without doing the work yourself, but it's still handy to know what's going on under the hood.
A common follow-up question is: what is the total cost of the loan?
Well we have four payments of R1491.58 and a final payment of R1491.56
Therefore the total cost is 4*1491.58 + 1491.56 = 7457.88
The total interest is found by subtracting off the original loan amount, so 7457.88 - 5000 = 2457.88 is the total interest
|
|
|
