Saving and borrowing go hand-in-hand. You save for your future, build a safety net against unexpected expenses, and borrow to supply yourself now with the things you wouldn’t otherwise be able to purchase outright, such as a car or a house. Responsible borrowing helps you to build a stable credit history. It signals to lenders that you’ll be a safe candidate for a loan. They’ll be more likely to offer you favorable terms on their money if they feel secure that they’ll receive it back without a hassle. That’s why it is important to create a strong credit presence if you lack one or repair a credit history that has taken a few dings. Responsible borrowing will help you do that.

Always do your research or speak with a financial expert when it comes to big decisions with your money. But there are a few steps you can take right away. Learn about your credit score. Limit the amount of debt that you can’t pay back right away, such as credit card debt and student loans. Always make your payments on time. Know how to handle high rate products like retail credit cards, and steer clear of credit that is easy to obtain but hard to pay back like so-called payday loans. Finally, understand the math behind loan products. It will help empower you to make wise decisions and keep you from falling prey to predatory lenders.

As you saw earlier in the text, the loan formula is the same as the payout annuity formula.

Spreadsheet Hands-On: Create a Loans Calculator

Step 1: Create the loans calculator

Since the loans formula is identical to the one for payout annuities, we can use our withdrawal calculator from the annuities worksheet. We need to make some changes though, so we’ll make a copy of the annuities worksheet and rename it Loans.

1. Right-click on the Annuities tab, move or copy, click the box to copy, then place it at the end of the list of sheets.
2. Delete lines 1-21. We won’t need the Annuity Calculator.
3. Rename line 1 as Loans Calculator.
4. Relabel cells A3 and A12 as Payment
5. Relabel the description of the interest field in cell C10 as “total interest paid.”
6. Empty all the non-formula cells. When you are finished, your sheet should look like the image below. 

We will use this sheet a little differently than we did the withdrawal annuity formula. Since we are concerned with paying money back, we’ll start at the bottom of the sheet and work up. First, let’s test our formulas using the numbers in the example above.

1. To find the monthly payment, enter the rate, years, balance, and periods in cells B13, – B16. The payment should automatically populate in cell B18 as in the image below. 
2. Now, let’s test the formulas in the top by entering the information again into cells B3 – B6. This time the balance of approximately 225,000 and the interest should automatically populate cells B8 and B10 as you can see below. 
3. You can see that a mortgage is expensive! It costs nearly as much in interest as the amount you borrowed. (At this point, you can also test your formula in cell B19 by typing the payment amount in B12 and seeing that 30 years are returned in B19.)

Amortization

Auto loans and mortgages use a process called amortization to decrease the loan balance while you pay the interest. Recall that the interest rate, such as the 5% rate in our example above, is an annual rate. Divide it by 12 monthly payments to get the portion of the rate payable per month.

[latex]0.5/12\approx 0.004167[/latex].

Each month, the monthly rate is multiplied by the amount of principal remaining to calculate the amount of that month’s payment that goes to the interest. The remainder is applied to the principal. This continues each month until the loan is paid off. Banks will provide the details of this process in a table called an amortization schedule. Let’s see how this plays out over time by adding an amortization schedule to our Loans sheet.

Step 2: Create an amortization schedule

1. Begin by labeling row 22 “Amortization Schedule” then label B23 – E23 as Payment, Principal, Interest, and Balance respectively.
2. In Cell A24, type 0. In cell A25, type 1. These will represent the payment number. We will start with the zeroth payment, to indicate the starting loan amount.

We will store the information for our amortization schedule in the first part of the loans calculator, where we use the inputs to the loans formula to compute the loan amount. We’ll reference these cells in the schedule we are creating to auto populate them based on the terms of the loan and monthly payment. It’s okay that the balance has a little spare change on the end. It won’t affect the general trend that we are looking for.

We need to handle an important detail as we reference the stored information when building our formulas. Spreadsheets will allow many rows in a column to be filled with a formula that computes subsequent rows based on information in previous ones by automatically changing the reference-cell location as it moves from row to row. We will do this to fill our schedule with these formulas in a moment. To prevent the spreadsheet from changing numbers we don’t want it to change, we’ll make some formula reference points static. We’ll do this by including a $ between the column and row coordinates that point to the monthly payment, rate, and periods.

1. In cell E24, type = B$8.
2. In cell B25, type = B$3.
3. In cell D25, we’ll use a formula to calculate the interest portion. Type =E24*(B$4/B$5).
4. In cell C25, we’ll take the difference between the amount paid in B25 and the interest portion in D25 to get the amount applied to the principle. Type =B25-D25.
5. Now, click and drag to highlight cells A25 – E25 and release the mouse. Grab the little square in the lower right hand corner and drag it straight down. You can take the information down as far as you wish. If you drag it all the way down to row 384 on the sheet you can see the loan balance reduce to zero! The image below shows one year of payments. Note how the interest paid per month decreases as the portion paid to the principal increases. 

Now you are ready to use your loan calculator to answer some questions.

Step 3: Use the loan calculator