Question: I acquired a loan of $20,000. The repayment period is 3 years at 10% per annum. Is there a way to work out the current repayment? I can afford to pay $1000 per month and would like to know the new repayment period too.
Answer: By first using the Payment function, and then using the solver option one can find a solution
Why: To calculate the new repayment period for a loan amount
Applies To: MS Excel 2003, 2007 & 2010
- Using the example given above, create a worksheet as per the screen below
2. Work out the current installment using the payment function. Select cell C7, (Monthly Payment), and type: =PMT(C6/12,C5,-C4)
3. Calculates the payment for a loan based on constant payments and a constant interest rate4.
4. PMT(rate, nper, pv, [fv], [type])
Rate Required. The interest rate for the loan
Nper Required. The total number of payments for the loan
Pv Required. The present value or the total amount that a series of future payments is worth now; also known as the principal
Fv Optional. The future value or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type Optional. The number 0 (zero) or 1 and indicates when payments are due
5. The answer will be $645.34
6. Now select Solver under Data –What If Analysis- Data tools group
7. If the solver option is not installed select the link below for instructions on how to add the solver
Click to access Solver%20Add–in.pdf
8. when you add the solver option select as given below
9. Make changes as illustrated below
10. Select the Add button and effect the changes below
11. Select OK
12. Select the Solve button and refer to the screen shot below
13. Select OK
The new repayment period will be 22 months. Thus one can easily calculate how long it will take to settle a loan amount based on new variables. The interest rate is envisaged not to exceed 15% in the stipulated period. The answer, sensitivity and limits reports have also been generated to the left of the active worksheet.
