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:

Step 5: Enter a formula to calculate the loan payment in Cell B6:

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

Share this content


Please login or register to join the discussion.

Jan 4th 2017 06:28

Isn't excel just such a godsend. If you didn't have expensive accounting and finance programs installed in your computer, you'd still be able to find a pretty decent tutorial online on how to draw up an amortization table so you can calculate just how much money you need when you sign yourself up for a loan!

Thanks (0)