Week One: Payment Calculator

Getting out of debt is an important goal for most people. Sometimes it takes a lifetime, but however, long it takes, the time will most likely be shortened by following a well$-$developed plan. Following is the problem statement:

In what ways might we show how long it will take to pay off a loan?

This document will provide step$-$by$-$step instructions to create a loan payment calculator to show how long it takes to pay off a loan. It allows the user to change payment amounts and interest rates. It can also be used to calculate the payment for a loan you are considering. The user enters the credit balance $($balance of the loan$),$ the APR $($interest rate$),$ the number of compounding periods $($it is $12$ if the loan requires monthly payments$),$ and the payments. The model calculates the effective interest rate, the number of months to pay off the loan, the total interest paid, and calculates the interest paid divided by the loan amount.

Note: The text entered in various cells has quotes to indicate the word or words that are entered. You dont type the quotes in Excel, they are there for the purpose of highlighting the text.

The step$-$by$-$step instructions follow:

Set column widths: Excel defaults to column widths of $8\mathrm{.}54.$ Set the following column widths:

A $=3\mathrm{.}0$

B $=8\mathrm{.}54($default width$)$

C $=15$

D $=12$

E $=15$

F $=3$

G $=15$

H $=15$

I $=15$

Model name: Enter the model name, Payment Calculator in cell B$2,$ increase the font size to $16,$ and format the words to make them bold by selecting Bold in the Font section of the ribbon. To increase the font size, select the down arrow in the Font section of the ribbon and select $16.$ See the picture below:

Center the model name: Put your cursor in cell B$2$ and highlight cells B$2$ to I$2$ by holding the left button on your mouse and dragging it to I$2.$ With all of the columns highlighted, select Merge and Center found in the Alignment section of the ribbon. See the picture below:

Enter text: In cell C$4,$ enter the following text: Important Note: Only enter data in the gold$-$colored cells. The green cells show the results. This text does not need to be centered.

Enter text: In cell C$7,$ enter the words Credit Balance

Enter text: In cell C$8,$ enter APR

Enter text: In cell C$9,$ enter the words Compounding Periods

Enter text: In cell C$10,$ enter the words Effective Interest Rate

Enter text: In cell C$11,$ enter the words, Monthly Payments

Enter text: In cell G$9,$ enter the words, Number of months to pay off loan

Enter text: In cell G$10,$ enter the words, Total Interest Paid

Enter text: In cell G$11,$ enter the words, Interest Paid $/$ Loan Amount

Enter text: In cell C$14,$ enter the word Balance, and center it in the column by selecting the Center button in the Alignment section of the ribbon. See the picture below:

Enter text: In cell D$13,$ enter the word Monthly, center it in the column and format it bold.

Enter text: In cell D$14,$ enter the word Payment, center it in the column and format it bold.

Enter text: In cell E$14,$ enter the word Interest, center it in the column and format it bold.

Enter text: In cell G$14,$ enter the word Principal, center it in the column and format it bold.

Enter text: In cell H$14,$ enter the words New Balance, center them in the column and format them bold.

Enter text: In cell I$13,$ enter the word Cumulative, center it and format it bold.

Enter text: In cell I$14,$ enter the word Interest, center it and format it bold

Data entry cell: Put your cursor in cell E$7$ and format the cell so that it has two decimal places and a comma separator. One easy way to do that is to select the Comma in the Number section of the ribbon. It defaults to two decimal places. See below:

Enter the number $3000$ as it is typed and Excel will format it as $3,000\mathrm{.}00.$

Shade the cell gold by selecting the drop$-$down arrow associated with the PaintBucket incon found in the Font section of the ribbon. The Theme Colors will appear. Select gold which is the color the red arrow is pointing.

Outline cell E$7$ with a thick black border by the down arrow associated with the Borders icon located in the Font section of the ribbon. Choose Thick Outside Borders from the list.

Data Entry Cell: Place your cursor in cell E$8.$ Format the cell to show percentages by selecting the $\%$ icon in the Number section of the ribbon. Add two decimal places by selecting the left arrow twice in the Number section of the ribbon. Enter $16\mathrm{.}99$ which will show as $16\mathrm{.}99\%.$ Shade the cell gold and add a Thick Outside Border.

Data Entry Cell: Place your cursor in cell E$9$ and enter the number $12.$ The number does not need to be formatted. Shade the cell gold and add a Thick Outside Border.

Data Entry Cell: Place your cursor in cell E$11.$ Format the cell using the comma and the default two decimal places. Enter the number $50$ which will show as $50\mathrm{.}00.$ Shade the cell gold and add a Thick Outside Border.

Formatted Cells: Put your cursor in cell E$10,$ format it green by selecting the same shade of green as you did gold. Add a Thick Outside Border. You will enter a formula in that cell in a later step. Do the same for cells, I$9,$ I$10,$ & I$11.$

Formatting: Place your cursor in cell B$2$ and highlight B$2$ to I$6.$ Highlight that section in the lightest shade in the gold column. Place your cursor in cell B$7$ and highlight cells B$7$ to D$12$ and shade the column in the lightest shade in the gold column. Shade cell E$12$ in light yellow. Highlight F$7$ to H$12$ and shade in light yellow. Do the same for cell I$12,$ I$7,$ and I$8.$

Formatting: Highlight the entire section, B$2$ to I$12$ and place a Thick Outside Border around this section.

Formatting: Place your cursor in cell B$13$ and highlight B$13$ to I$14.$ Format that section as dark gray by choosing the darkest gray in the left hand column of colors. Highlight the same area and change the Font color to white. Put a Thick Outside Boarder around both rows in each column. For example, highlight cells B$13$ and B$14$ and place a Thick Outside Border around them. Do the same for column C$,$ D$,$ E$,$ include F & G together, H$,$ and I.

Formatting: Place your cursor in cell B$15$ and highlight B$15$ to I$1250.$ Highlight the entire section medium gray from the left and column and format the cells using the comma in the Number section of the ribbon. You will add formulas to those columns in a future step.

Payment Number: Enter the number $1$ in cell B$15,$ in cell B$16$ enter $=$IF$($H$15<=0,,$B$15+1).$ The formula will cause the number of payments increment by one until the new balance in column H is less than or equal to zero. At that point, the payment numbers will cease to increment.

Calculate Effective Interest Rate: The formula for the effective interest rate is below:

In cell E$10,$ enter $=((1+$E$8/$$E$$9)^($$E$$9))-1$ and then format cell E$10$ to show percentage with one decimal points. The purpose of the $ in the formula is that is the Excel symbol to create an absolute reference.

Number of months to payoff loan: In cell I$9,$ enter $=$MAX$($B$15$:B$1236).$ The MAX command in Excel is the command to find the highest value in the range.

Total Interest Paid: In cell I$10,$ enter $=$SUM$($E$15$:E$1236).$ The SUM command in Excel adds the values in the range.

Interest Paid $/$ Loan Amount: in cell I$11,$ enter $=$I$10/$E$7.$

Balance: In cell C$15,$ enter $=$E$7.$ In cell C$16,$ enter $=$H$15$

Monthly Payment: In cell D$15,$ enter $=$IF$($C$15=0,0,$IF$($$E$$11=,$IF$($C$15*$#REF!$<$#REF!,#REF!,C$15*$#REF!$),$$E$$11))$

Interest: In cell E$15,$ enter $=$C$15*$$E$$8/12.$

Principal: In cell G$15,$ enter $=$D$15-$E$15.$ In cell G$16,$ enter $=$IF$($C$16=0,0,$D$16-$E$16).$

New Balance: In cell H$15,$ enter $=$C$15-$G$15.$ In cell H$16,$ enter $=$IF$($C$16-$G$16<0,0,$C$16-$G$16)$

Cumulative Interest: In cell I$15,$ enter $=$E$15.$ In cell I$16,$ enter $=$I$15+$E$16,$

This is what your spreadsheet should look like at this point. The reason for the IF statements in the Monthly Payment, Principal, and New Balance columns is because we want the calculations to cease when the loan is paid in full. The syntax for the IF statement is:

The final step is to copy the formulas in row $16$ to the bottom of the range. We do this by highlighting columns B$,$ C$,$ D$,$ E$,$ F$,$ G$,$ H$,$ and I in row $16$ and then select Copy. Then we highlight cell B$17$ to cell I$1236$ and then select Paste.

Now your spreadsheet should look like this:

For this loan of $$3,000,$ if $$50$ monthly payments are made, the loan will be repaid in $135$ months and the total interest paid is $$3,735\mathrm{.}21.$