GoGreen Caf, a local small business will be borrowing from a local bank a loan of $12,000 for 18 months at an APR of 7 25 The bank will accept a loan repayment schedule of equal, end of the month payments with interests to be compounded monthly The GoGreen Caf owner, Nancy, has asked you to explain to her how loan amortisation works and how to calculate periodic interests and repayments Present your answers in the following space Using Excel , prepare a monthly loan amortization table for the entire loan period The table should display the values for the following columns the time period, (equal) periodic loan repayment, interest expense, principal repayment and loan balance (2 marks) Months Periodic Loan Payment ($) Interest Expense ($) Principal Payment ($) Outstanding Balance ($) From your Excel table, paste your answers for the first and final periods here In reference to your Excel amortisation table, clearly show the following The Excel function and values used to calculate the (equal) periodic loan payment (0 5 mark) Present your answer under this column (type n a where it is non applicable) Excel function used (ex PV, FV or PMT, etc) Excel Function used pv fv pmt rate nper Clearly state the formula used to calculate the interest expense of a given period (0 5 mark) Present your answer under this column Formula used Clearly state the formula used to calculate the principal payment of a given period (0 5 mark) Present your answer under this column Formula used Clearly state the formula used to calculate the outstanding balance of a given period (0 5 mark) Present your answer under this column Formula used Clearly explain to Nancy how the entire amortisation process works and what the end loan balance at the final period means to her financial position as a borrower Use relevant figures from your calculations and Excel table to demonstrate your point (Word limit 150 words answers beyond word limit will NOT be marked ) (3 marks) Present your answers here

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 23, 2024

GoGreen Caf, a local small business will be borrowing from a local bank a loan of $12,000 for 18 months at an APR of 7.25%.

GoGreen Caf, a local small business will be borrowing from a local bank a loan of $12,000 for 18 months at an APR of 7.25%. The bank will accept a loan repayment schedule of equal, end of the month payments with interests to be compounded monthly. The GoGreen Caf owner, Nancy, has asked you to explain to her how loan amortisation works and how to calculate periodic interests and repayments. Present your answers in the following space.

Using Excel, prepare a monthly loan amortization table for the entire loan period. The table should display the values for the following columns: the time period, (equal) periodic loan repayment, interest expense, principal repayment and loan balance. (2 marks)

Months

Periodic Loan Payment ($)

Interest

Expense

($)

Principal

Payment

($)

Outstanding

Balance

($)

From your Excel table, paste your answers for the first and final periods here:

In reference to your Excel amortisation table, clearly show the following.

The Excel function and values used to calculate the (equal) periodic loan payment. (0.5 mark)

	Present your answer under this column (type n/a where it is non-applicable)
Excel function used: (ex: =PV, =FV or =PMT, etc)	Excel Function used:
pv:
fv:
pmt:
rate:
nper:

Clearly state the formula used to calculate the interest expense of a given period. (0.5 mark)

	Present your answer under this column
Formula used:

Clearly state the formula used to calculate the principal payment of a given period. (0.5 mark)

	Present your answer under this column
Formula used:

Clearly state the formula used to calculate the outstanding balance of a given period. (0.5 mark)

	Present your answer under this column
Formula used:

Clearly explain to Nancy how the entire amortisation process works and what the end loan balance at the final period means to her financial position as a borrower. Use relevant figures from your calculations and Excel table to demonstrate your point. (Word limit: 150 words; answers beyond word limit will NOT be marked.) (3 marks)

Present your answers here: