How To: The Excel Auto Payment Calculator

How To: The Excel? Auto Payment Calculator

Document ?2013 Thor Kolner. All rights reserved.

Challenge:

A co-worker's car was damaged during a collision. The good news was there were no injuries. The bad news was she unexpectedly found herself in the new car market.

There was still some life left in the car, but being a single mother of two small children, she needed reliable transportation. She had a small amount saved up for a new car as well.

What she wanted to know before she went new-car shopping was how much a new car payment would be.

Solution:

We built a simple report using Excel's Payment (PMT) function. The spreadsheet includes cells where she can enter the interest rate, number of payments (terms), the amount of her down payment, and the amount of her trade in.

She transferred the completed workbook to her smart phone. As she traveled from dealer to dealer, she entered the new car price and other key pieces of information. The worksheet automatically calculated what her monthly payment would be.

Skills Required:

A. Experience using Microsoft? Excel. B. Experience entering an arithmetic calculation. C. Experience working with functions. D. Basic understanding of how principle and interest work.

1|Page



Try It!

1. Download the Auto Payment Calulator.xlsx workbook from the web site. 2. Fill in cells B1 through B6 with information gathered at the dealership. Note: In cell B2, enter the

number payments you'll make each year (example: 12). 3. In cell B7, calculate the amount borrowed. Enter the following formula:

=B4-B5-B6

4. Click in cell B8. Click the Formulas tab. Browse the list of Financial functions, and click PMT.

5. Click in the Rate field, and then click cell B1 (Interest Rate).

2|Page



6. Enter a division operator (/) and then click cell B2 (Payments Per Year).

7. Click in the Nper field, and then click cell B3 (Terms in Months). 8. Click in the Pv field, and then click cell B7 (Total Borrowed).

3|Page



9. Click OK.

As she continued shopping for a new car, truck, or van change the value in cell B4 (Vehicle Price) and the monthly payment will automatically adjust. The payment amount will also change automatically if the interest rate (B1) changes, or you modify the number of payments you'll be making each year (B2).

The payment calculator workbook is an interactive way of quickly calculating the potential monthly payment of a new vehicle purchase. However, it is not designed to be a legal contract or loan generator. Use it as you gather other new vehicle information to help make the wisest choice.

4|Page



 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download