Finance Spreadsheet Project, Part 1

You will develop this project in two parts, increasing in complexity.

For this assignment, first create a new folder called Finance inside your CS120/Projects folder.

Create a new Google Sheets file called Loan Tables inside the Finance folder. You will create 2 sheets inside this one file.

For both sheets, include columns for month, date, payment, interest amount, balance reduction, and balance. You may also need places for the APR, payment, number of years, total interest paid, and total paid on some of the sheets. So, your sheets could look something like the table below, but with more rows.

 Now here is what you need to do.

Sheet 1: Loan payments (choose monthly payment, calculate length of loan)

For this sheet, you will choose a particular monthly payment for a loan balance then fill down a loan table, one row for each month, to determine how long it will take to pay off the loan. As you fill down the table, you will most likely not end up with a $0.00 balance. You will need to fill down the table until you first see a month that ends with a negative balance, remove that month, then calculate (write a formula) what the actual final payment should be so that you don't pay more than the balance of the loan. Use clear column headings like the example above.

• Choose a Balance, APR, and Monthly Payment based on the criteria below:
• $1000 ≤ Balance ≤ $3000
• 5% ≤ APR ≤ 24%
• $50 ≤ Monthly Payment ≤ $200
• Create formulas for each column. Fill these formulas down until your balance changes from positive to negative. There will be one row for each month of the loan. Here are some tips on the formulas:
  • Interest Amount = (previous balance)*(interest rate)*(1/12)
  • Balance Reduction = (current payment) - (interest amount)
  • Balance = (previous balance) - (balance reduction)
• In the month that the first negative balance happens, you will need to combine the previous balance with the interest due to calculate the final payment. If done correctly, this final payment will lead to a zero balance.
• Find the number of periods, last payment, total interest paid, and total amount paid. Calculate each of these in a cell with an adjacent label.
• How many months did it take to pay off the entire balance?
• Since you picked the monthly payment, the last payment must have been less than what you usually paid each month. What was it?
• How much interest did you pay in all?
• How much money did you pay back in all, i.e., what was the sum of your payments?

Sheet 2: Amortization payments (choose length of loan, calculate monthly payment)

For this part you will choose a number of years in which to pay off a loan and use the PMT Links to an external site. function to calculate the monthly payment you'll need. You'll then follow the same procedure you did for Sheet 1 to fill down the table with one row for each month until you reach a balance of $0.00. Note that in this table, you should reach $0.00 exactly after the number of years you chose. For example, if you chose 5 years, you should reach a balance of $0.00 after exactly 60 months.

• Choose a Balance, APR, Number of Years:
• use a different balance and APR than those you used for sheet 1.
• $1000 ≤ Balance ≤ $3000
• 5% ≤ APR ≤ 24%
• 2 ≤ Years ≤ 10
  
• Create formulas for each column. Fill these formulas down until your balance is zero. You will have 12 rows for each year of your loan.e.g., for 3 years there would need to be 36 rows of calculations..
• Find the payment, total interest, and total amount paid. Calculate each of these in a cell with an adjacent label.
• Use a Google Sheets function to calculate the periodic payment for a loan based on constant-amount periodic payments and a constant interest rate (PMT Links to an external site.) . 
• How much interest did you pay in all?
• How much money did you pay back in all, i.e., what was the sum of your payments?

Important Notes

Before submitting your work, review both sheets and make sure you can answer 'yes' to all of the following questions:

• Did you use the same column headings on both sheets?
• Did you clearly mark your APR, beginning balance, and monthly payment on both sheets?
• Did you clearly state the number of months it took to pay off the loan in sheet 1?
• Did you clearly state the number of years you chose for the length of the loan in sheet 2?
• Did you use cell references rather than copying actual numbers or typing numbers into your table cells?
• On sheet 2 did you correctly use the PMT formula to calculate the monthly payment?
• Did you end up with a $0.00 balance in the correct number of months on sheet 2. For example, if you chose to pay off the loan in 3 years, you should reach a $0.00 balance exactly in 3*12 = 36 months.
• Does your sheet 1 not end with a negative balance? The last month should contain a simple calculation for the final payment so that the balance is $0.00. The last month's payment for sheet 1 will most likely be different than all the previous months!
• Did you remember to calculate and clearly label the following for both sheets?
  • Total amount paid
  • Total interest paid

Once you've completed both parts and have reviewed your work, submit a shareable link to your Loan Tables spreadsheet file. Make sure that file is in your CS120/Projects/Finance folder.

Video Tutorial Links to an external site.