ZuoTi.Pro
Question

This worksheet will compute the monthly value of an amortized loan for 36 months. This will...

  1. This worksheet will compute the monthly value of an amortized loan for 36 months. This will be a worksheet in which the loan value or principal, interest rate, and monthly payment can be changed and the worksheet will automatically update. Within the worksheet there will be some absolute cell referencing that is needed and some relative cell referencing that is needed. You will need to determine which is appropriate.

    1. Title the worksheet "Amortization of Car Loan" in cell A1. Leave a blank row under the title.
    2. Enter Today's Date in cell G1 so that it updates each time the file is updated in the spreadsheet.

  2. In row five of this worksheet create cells labeled "Amount of Loan," "Interest Rate," and "Monthly Payment," with two columns between each label. Invent reasonable values to put in the three cells below each title as a starting place. These values are temporary values to help you see if your worksheet is working as you create it. For example, you may want to enter $5000 as the loan amount, .065 as the interest rate, and $150 as the monthly payment.

  3. Leave two empty rows below the cells created in the prior step, then label four columns as "Month," "Current Principal," "Interest Due," and "New Principal."

    1. In cells under the "Month" title place the numbers from 1 to 36. Put the number 1 in the first cell and then use cell referencing and cell copying to create the rest of the column by using a formula that adds one to the cell above.
    2. In the first cell under "Current Principal" title reference the value in the "Amount of Loan" cell.
    3. In the first cell under the "Interest Due" column use a formula to calculate the interest that is due on the first value in the "Current Principal" column. You will need to multiply the "Current Principal" by the "Interest Rate," divided by 12. (For the rate, reference the rate value entered in step 2. The rate is a yearly rate and the time is 1/12 of a year.) Use cell referencing in this formula. You will need to use an absolute reference to refer to the "Interest Rate" value. (The formula is I = p*r*t.)
    4. In the "New Principal" column use a formula to compute the new amount due by taking the "Current Principal" and adding the "Interest Due" and subtracting the "Monthly Payment." Again, you must use absolute cell referencing for this.
    5. Make the next "Current Principal" equal to the "New Principal" from the month before.
    6. Complete the second row of the data by copying the rest of the formulas from the cells in the row above. (Check that your references are correct.)
    7. Complete the worksheet by copying all three of the formulas down for the remaining 36 months.
    8. At this point you should be able to determine amounts due after 36 months by changing only the values of the "Amount of Loan," "Interest Rate," and "Monthly Payment." Test your worksheet by entering $15000 into the "Amount of Loan," 0.08 into the "Interest Rate," and $470 as the "Monthly Payment." If you have completed everything correctly the amount due after 36 months is $1.84! The interest due for the first month should be $100.

  4. Place the letters "a," "b," "c," and "d" in a column to the right of the amortization work. Use your worksheet to determine the following and put the answer next to the letter:

    Explain this.

    1. After 36 months, how much will be owed on a loan of $15,000 at 6% if the monthly payment is $220?
    2. Find the monthly payment to the nearest dollar (use trial and error and change the payment until you get it) that will pay off a $123 ,000 loan in 36 months if the interest rate is 7%.
    3. Find the monthly payment to the nearest dollar (use trial and error) that will pay off a $7,000 loan in 36 months if the interest rate is 4%.
    4. After 36 months, what happens to a $25,000 loan at 8% if the monthly payment is $135?
  5. In a short paragraph typed into your worksheet below the responses above, explain the difference between relative and absolute cell referencing. Use complete sentences and merge the cells so that the paragraph can be easily read.
  6. Check your worksheet for clarity and layout. Make sure that the worksheet is easy to read and that the data is easy to interpret.
  7. Format your worksheet to print on one page in portrait.
  8. Check your worksheet for spelling errors and correct any spelling errors that you find.
  9. Save the workbook file. Keep a copy of this assignment for yourself. You will be using this assignment later in the course.
business   accounting   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1
Amortisation of Car Loan 27 April 2020
(As per the requirements of part 'd')
Amount of loan (in $) Interest rate (%) Monthly payment($)
            25,000.00 8%                135.00
(As per the requirements of part 'd')
Month Current principal (in $) Interest due (in $) New Principal (in $) Questions Answers (amount in $) Remarks
1               25,000.00                 166.67                25,031.67 a                   9,296 Amount due after 36 months, if the principal amount of loan is $15000, interest required to be paid is at 6% per annum and the monthly payment is $220.
2               25,031.67                 166.88                25,063.54 b 798 Monthy payment required to pay off the loan in 36 months when the principal amount to be repaid is $123,000 and the rate of interest is 7% per annum.
3               25,063.54                 167.09                25,095.63 c                      207 Monthy payment required to pay off the loan in 36 months when the principal amount to be repaid is $7000 and the rate of interest is 4% per annum.
4               25,095.63                 167.30                25,127.94 d                26,284 The value of loan payable would have risen to $26283.63 as the interest to be paid per month has always been more than the monthly repayment of principal.
5               25,127.94                 167.52                25,160.46
6               25,160.46                 167.74                25,193.19 Difference between absolute and relative cell referencing;
7               25,193.19                 167.95                25,226.15 There are mainly two types of cell references relative and absolute. The main difference between the two is that a relative cell reference move when you copy them, which means the cell refered does not remain constant, however an absolute cell reference does not move when copied, which means the cell refered would remain the same. An absolute cell reference is represented by a "dollar" sign in front of the cell reference.
8               25,226.15                 168.17                25,259.32
9               25,259.32                 168.40                25,292.72
10               25,292.72                 168.62                25,326.34
11               25,326.34                 168.84                25,360.18
12               25,360.18                 169.07                25,394.25
13               25,394.25                 169.29                25,428.54
14               25,428.54                 169.52                25,463.07
15               25,463.07                 169.75                25,497.82
16               25,497.82                 169.99                25,532.81
17               25,532.81                 170.22                25,568.02
18               25,568.02                 170.45                25,603.48
19               25,603.48                 170.69                25,639.17
20               25,639.17                 170.93                25,675.10
21               25,675.10                 171.17                25,711.26
22               25,711.26                 171.41                25,747.67
23               25,747.67                 171.65                25,784.32
24               25,784.32                 171.90                25,821.22
25               25,821.22                 172.14                25,858.36
26               25,858.36                 172.39                25,895.75
27               25,895.75                 172.64                25,933.39
28               25,933.39                 172.89                25,971.28
29               25,971.28                 173.14                26,009.42
30               26,009.42                 173.40                26,047.81
31               26,047.81                 173.65                26,086.47
32               26,086.47                 173.91                26,125.38
33               26,125.38                 174.17                26,164.54
34               26,164.54                 174.43                26,203.98
35               26,203.98                 174.69                26,243.67
36               26,243.67                 174.96                26,283.63
0 0
Know the answer?
Add Answer to:
This worksheet will compute the monthly value of an amortized loan for 36 months. This will...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT