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

Nov 5Join CPA thought leader and peer reviewer Rob Cameron and learn ways to improve the outcome of your peer reviews while maximizing the value of your engagement workflow.
Nov 18In this session Excel expert David Ringstrom, CPA tackles what to do when bad things happen to good spreadsheets.
Nov 19How do you minimize redundant work and unnecessary steps to maximize the amount of work moving through your firm?
Nov 20Kristen Rampe will share how to uncover new opportunities with your clients by asking powerful questions.