Premium Sample: Excel Tip: Create a Quick Loan Calculator in Excel

You can use Excel to help figure out what your loan payment would be should you decide to borrow money. Here's a quick way to set up a loan payment calculator in your Excel worksheet:

Step 1: Enter the following titles in six consecutive rows:
Cell A1 Price
Cell A2 Downpayment
Cell A3 Principal
Cell A4 Interest
Cell A5 Years
Cell A6 Payment

Step 2: These titles will become the cell names for the cell to the right of each cell containing a title. Highlight the cells containing titles and the cell to the right of each of those cells (12 cells altogether). Choose Insert | Name | Create from the Excel menu. The Create Names window will appear with "Left column" checked. Click OK. The names have been assigned.

Step 3: Enter known amounts in the cell to the right of each of the cells containing titles. For example:
Cell B1 10000
Cell B2 20%
Cell B4 6.5%
Cell B5 4

Step 4: Enter a formula to calculate loan principal in Cell B3:
=Price*(1-Downpayment).

Step 5: Enter a formula to calculate the loan payment in Cell B6:
=PMT(Interest/12,Years*12,Principal).

Once the calculator is in place you can substitute amounts for Price, Downpayment, Interest, and Years and the calculations will update automatically.

You may like these other stories...

By Alexandra DeFelice Can you sleep soundly at night knowing your firm is safe from data security breaches? We've all heard the stories of stolen laptops, hacked computers, and begrudged employees leaving the firm...
Two upcoming web seminars will focus on security related issues. The first, scheduled for October 25, will look at proven methods for measuring security. The second, to be held November 1, will explore the next generation of...
The Small Business Administration (SBA) released figures on its post-9/11 disaster lending program showing that $245 million of the total $1.2 billion lent is currently in default, representing a 20.4 percent default rate....

Already a member? log in here.

Upcoming CPE Webinars

Aug 26
This webcast will include discussions of recently issued, commonly-applicable Accounting Standards Updates for non-public, non-governmental entities.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.
Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.
Sep 11
This webcast will include discussions of commonly-applicable Clarified Auditing Standards for audits of non-public, non-governmental entities.