How to Create a Loan Payment Spreadsheet or Calculator

Have you ever gotten a loan and wondered how your loan lender calculates your monthly payments? What is the total loan payment equal to after interest if you just make the minimum monthly payments? Once you have created this useful spread sheet you can keep track of your loan payments and how much you still owe. This is also a great way to shop around for a loan that will give you the best rate and terms. It is helpful to be able to see the effect of different loan terms that may be quoted to you.

Difficulty: Easy

Instructions

Things You'll Need:

• Computer
• To know how much the loan is for
• The APR on the loan you are interested in or you have.
• To know how many years you have to pay off the loan.
• The program Microsoft Works Spreadsheet

1. 
    

   Go to the start menu and open your Microsoft Works Spreadsheet.

2. 
    

   Once your spreadsheet is open you will notice it has columns labeled with letters and rows labeled with numbers. This is how we will navigate through the spreadsheet to create the loan worksheet. For example the picture shows space (cell) C4 highlighted. In order to type in one of these spaces just left click on the space with the mouse and then start typing.

3. 
    

   To begin we will first input the labels we will use.
   *In space A1 type: Loan Amount
   *In space A2 type: Annual Interest Rate
   *In space A3 type: Monthly Interest Rate
   *In space A4 type: Number of Years to Pay Back
   *In space A5 type: Total Number of Payments
   *In space A6 type: Monthly Payment
   *In space A7 type: Total of All Payments

4. 
    

   Once you have typed in all of the labels left click on the letter A with your mouse. This will then highlight the whole column.

5. 
    

   Then right click on the mouse key to open up a menu. Search for the format option in this menu and click on it.

6. 
    

   This opens up a different menu called Format Cell. Select the alignment option and then search for a check box labeled Wrap text with in cell. Use your mouse and click on the box. You should see a check mark appear in the box after you clicked it. Then press ok. This will format the text in column A to fit into the cells

7. 
    

   While column A is still highlighted click the buttons Ctrl and B at the same time on your keyboard to highlight everything in column A.

8. 
    

   Left click on cell B1 to highlight it. Then right click cell B1 to open back up the same menu you used in step 5. Select the format option. Then in the Format Cell menu left click on the Number option. Select the currency option in the list under Select Format Type. Make sure the Set Decimal Places is set to 2. Then left click on the OK button. Repeat this step for cell 6 and cell 7.

9. 
    

   Left click on cell B2 and open up the Format Cell menu just as you did in the previous step. Select the Number option and select Percent located under the list Select Format Type. Make sure the Set Decimal Place is set to 2. Then Click ok. Repeat this step for cell B3.

10. 
     

    Select cell B1 and type in the amount of your loan. Then in cell B2 type in the APR of your loan. I used $10,000 as the loan amount and 7.50% as the APR just for example.

11. 
     

    Left click on cell B3 and type =B2/12 and click the Enter button. This formula will calculate the monthly interest rate you will be paying on the loan.

12. 
     

    Select cell B5 and type =B4*12 and click the Enter button. This formula will calculate the number of months over which the loan will be repaid.

13. 
     

    Select cell B4 and enter in the total number years you have to pay off this loan. For example, I used 3 years.

14. 
     

    Select B7 and type B5*B6 , then click Enter. This will calculate how much in total you will pay on the loan including the APR.

15. 
     

    Left click on cell B6. At the top of the spreadsheet select the Insert Option to open up a drop down box. Look for Function option and select it.

16. 
     

    This will open up a menu called Insert Function. Select the Financial option in the list below Select a category. Then select the PMT option listed under Choose a function and press the Insert button.

17. 
     

    A formula will appear in the cell that looks like
    =PMT(Principal,Rate,Term).

18. 
     

    Erase the word Principal and in its place type in B1 so the formula looks like =PMT(B1,Rate,Term). Then erase Rate and Term. Replace these words with B5 and B3 so the formula looks like =PMT(B1,B3,B5). Then press the Enter key on your key board. If you used the same numbers as I did than $311.06 should appear in box B6 and $11,198.24 should appear in box B7.

19. 
     

    After entering all of the numbers and formulas I have given check to see that your spreadsheet calculated the exact numbers as I have illustrated in the picture. Once you have a good working version of this spreadsheet you can change the numbers in B1, B2 and B4 as needed to come up with your loan calculations.

Tips & Warnings

• Don't forget to save your spreadsheet as you go. You don't want to loose all of your hard work!

[sonni57]

on 3/13/2009 Great detailed article thanks for the info.

[msc3]

on 3/11/2009 While it doesn't include taxes and insurance, Microsoft Excel has a really useful mortgage calculator template. It will show you what the monthly payment would be (without taxes and insurance). My favorite part is that you can add all kinds of different additional payments and easily see how that would affect the payoff timeframe. I made a demo of how it works at www.yourtwobits.com.

[tachic]

on 3/7/2009 This was a great read, thanks.

[sonni57]

on 3/5/2009 It's a A article with 5 stars and all the fixings

[Gracie1402]

on 3/5/2009 I like the screen shots- that helps a lot thanks!