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....

Upcoming CPE Webinars

Apr 17
In this exciting presentation Excel expert David H. Ringstrom, CPA shares tricks that you can use with pivot tables every day. Remember, either you work Excel, or it works you!
Apr 22
Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
Apr 24
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
Apr 25
This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.