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

Already a member? log in here.

Upcoming CPE Webinars

Oct 30Many Excel users have a love-hate relationship with workbook links.
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 12This webcast presents basic principles of revenue recognition, including new ASU 2014-09 for the contract method. Also, CPAs in industries who want a refresher on revenue accounting standards will benefit.
Nov 18In this session Excel expert David Ringstrom, CPA tackles what to do when bad things happen to good spreadsheets.