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

Earlier this week I presented the Chart Edition of AccountingWEB’s High Impact Excel webinar series. One of the many topics I covered was the Sparklines feature, which was first introduced in Excel 2010. Several...
On January 30, I led a free, one-hour webinar, High Impact Excel: Pivot Table Edition. If you missed the presentation, it’s too late to get CPE credit, but you can watch an on-demand recording. After the webinar, I...
By David Ringstrom, CPA In Part 1 of this series I showed how to use a custom number format to conditionally display decimal places. Although the technique is simple, the downside is it may not work in every situation....

Upcoming CPE Webinars

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.
Apr 30
During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.
May 1
This material focuses on the principles of accounting for non-profit organizations’ expenses. It will include discussions of functional expense categories, accounting for functional expenses and allocations of joint costs.