Business Automation and Software Blog

Excel Tips and Tricks: Solver (Recovered)

Posted by Robert Baran on Fri, Aug 24, 2012 @ 01:11 PM

Excel Tips and Tricks / Business Reporting 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

1. Using the example given above, create a worksheet as per the screen below.

Excel Tips and Tricks

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 rate

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:

http://www.alchemex.com/Resources/EOS%20Previous%20Tips%20And%20Tricks/Solver%20Add–in.pdf

8. When you add the solver option select as given below.

Excel Tips and Tricks

9. Make changes as illustrated below.

Excel Tips and Tricks

10. Select the Add button and effect the changes below.

Excel Tips and Tricks

11. Select OK.

12. Select the Solve button and refer to the screen shot below.

Excel Tips and Tricks

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.

If you would like further assistance with your business reporting, please contact us today for a complimentary consultation.

Contact PositiveVision ► ► for a Complimentary  Reporting Consultation

Learn more about PositiveVision's Management and Operational Business Reporting services...

Topics: Business Reporting, Excel Tips and Tricks