You plan on borrowing $14000 at 16% APR for 5 years. Use Web Loan Calculator to find the monthly payment. Find the total interest paid on this loan for the first 20 months.
Set up a spread sheet to answer. Here is an example of what it should look like. All cells in lower portion are formulas. You can use my examples numbers to test your spread sheet before you answer the questions. This is what is known as an amortization of the loan.
What is the monthly payment found on a bank web site?
$
per month. (rounded to nearest cent)
After 20 months of payments, how much total interest has been paid for this loan? Use a spreadsheet to answer, round to nearest cent.
$
of interest in the first 20 payments.
I used
http://www.calculatorweb.com/calculators/loancalc.shtml
and got a payment of $3404.53 , but I learned nothing about the actuarial math involved.
From your data:
Present value = 140000
n = 5(12) = 60
i = .16/12 = .013333...
(ridiculously high rate for current rates, time to get a new text)
Payment = p
p(1 - 1.01333..^(-60) )/.01333... = 140000
p(41.12170722..) = 140000
p = 3404.53
value of debt after 20 periods if no payment had been made:
140000(1.01333..)^20 = $182,462.92
amount of 20 payment at that time
= 3404.53(1.01333..^20 - 1)/.01333.. = $77,446.22
outstanding balance after 20 periods
= 182462.92 - 77446.22 = $105,016.70
actual amount paid off = 140,000 - 105,016.70
= $34,983.30
actual amount paid = 20(3404.53) = $68,090.60
interest = $68,090.60 - $34,983.30 = $33,107.30
The last paragraph of calculations would be totally invalid and unacceptable to an actuarial mathematician, since you cannot add up monetary values that are not in the same "time spot" on a time-line.
In your spread sheet, there might be a splitup of the monthly payments into "interest" and "repayment".
The sum of the column for "interest" for 20 lines should be 33107.30
To find the monthly payment using a web loan calculator, you can follow these steps:
1. Go to a reputable bank or financial institution's website that offers loan calculators. Examples include Bank of America, Wells Fargo, or Chase.
2. Select the loan or mortgage calculator option on the website.
3. Enter the loan amount, in this case, $14,000, as the principal or loan amount.
4. Enter the annual interest rate, which is given as 16% APR. Convert this to a decimal by dividing it by 100 (16/100 = 0.16), then divide by 12 to get the monthly interest rate (0.16/12 = 0.0133).
5. Enter the loan term in years, which is given as 5 years.
6. Press the calculate button on the loan calculator.
The website's loan calculator will then provide you with the monthly payment amount. Use this value to answer the first question in the spreadsheet.
To find the total interest paid on the loan for the first 20 months, you can set up a spreadsheet as follows:
- Column A: Month number (from 1 to 60, representing the 5-year loan term)
- Column B: Monthly payment (use the value obtained from the loan calculator)
- Column C: Remaining balance (initially $14,000, decreases with each payment)
- Column D: Interest payment for the month (calculated by multiplying the remaining balance by the monthly interest rate)
- Column E: Principal payment for the month (calculated by subtracting the interest payment from the monthly payment)
- Column F: Total interest paid after each month (running sum of the interest payments)
The formula for the remaining balance in cell C2 would be "=C1-E2" (assuming the header row is in row 1).
The formula for the interest payment in cell D2 would be "=C1*B2" (assuming the header row is in row 1).
The formula for the principal payment in cell E2 would be "=B2-D2" (assuming the header row is in row 1).
The formula for the total interest paid after each month in cell F2 would be "=SUM(D$2:D2)" (assuming the header row is in row 1 and F1 is the "Total Interest" label).
Copy these formulas down the respective columns for all 60 months.
After 20 months, the total interest paid will be given by the value in cell F20. Round this amount to the nearest cent to answer the second question in the spreadsheet.