Please show how to make the spreadsheet and please leave answer C,D,G,H in PERCENTAGE format.

Loan Problem Instructions:

Follow these instructions carefully. You dont want to lose points for not following instructions.

1. Go to Blackboard / Course Documents/ Loan Problem / Questions.
2. Download the ZIP file, open it, and find the question with your ID number (since this number is secret, your full privacy is maintained).
3. Do only your problem (no need to do all the 1,000+, also dont pick someone elses number)
   1. You can work in groups and ask for outside help
   2. Have someone look over your work
4. If there is an issue with your problem, email me.
5. If you are having problems doing the problem, post on Blackboard / discussion board (do not email me).
6. Make sure Excel is set to Calculate automatically. To enable "auto calculate" in Excel:
   1. for Windows: File/Options/Formulas/Workbook Calculation set to Automatic or hit F9 key
   2. for Mac: Excel-->Preferences --> Calculation -->Automatically or Command + = (I found this in Google and did not try it)
7. Before the cutoff time Sunday night, May 3, 11:59 PM, submit your answers at: https://forms.gle/L8fxzsuMQ4TGoGCNA. Please follow instructions carefully. Use Excel "Decrease Decimal" to do the rounding for you. You will be required to submit your worksheet (so we can verify that it was not copied).
8. Suggestion: Submit your id# and answers by cutting and pasting from your excel spreadsheet instead of copying (copying can introduce errors).
9. If you change your mind and discover an error, you may re-input your solution as many times as you like till the cutoff time - only the last solution is graded
10. I hope to grade all problems within 48 hours of the cutoff time

Good Luck

Professor Davis

There is no possibility of a late submission.

Use the following check digits:

Sum Total Cash flow = Sum interest paid

Owe at end = $0

Sum Principal repaid = amount borrowed

Sum Total Payment = Sum Interest paid + Sum principal repaid

IRR of the cash flows = interest rate

Suggestion: first on a blank spreadsheet build the 5 year problem presented in the lecture (sample is included with lecture and also on Blackboard). Once you built that, start again with a blank spreadsheet and do your problem.

You borrow money on a self liquidating installemnt loan (equal payments at the end of each year, each payment is part principal part interest) Loan $757,000 Interest Rate 16.50% Life (years) Date of Loan January 1, 2020 59 Use the installment method - not straight line Do NOT round any interrmediate numbers. Do NOT turn this into a monthly problem. a) What is the annual payment? $124,920.25 b) What are the total interest payments? $6,613,295.03 c) After 22 payments have been made, what percentage of the total interest has been paid (round to the nearest percentage point)? d) After 22 payments have been made, what percentage of the total principal has been paid (round to the nearest percentage point)? Redo the problem if the interest rate is 2.00% (for a well designed spreadsheet this should take 30 seconds) e) What is the annual payment? f) What are the total interest payments? g) After 22 payments have been made, what percentage of the total interest has been paid (round to the nearest percentage point)? h) After 22 payments have been made, what percentage of the total principal has been paid (round to the nearest percentage point)? You borrow money on a self liquidating installemnt loan (equal payments at the end of each year, each payment is part principal part interest) Loan $757,000 Interest Rate 16.50% Life (years) Date of Loan January 1, 2020 59 Use the installment method - not straight line Do NOT round any interrmediate numbers. Do NOT turn this into a monthly problem. a) What is the annual payment? $124,920.25 b) What are the total interest payments? $6,613,295.03 c) After 22 payments have been made, what percentage of the total interest has been paid (round to the nearest percentage point)? d) After 22 payments have been made, what percentage of the total principal has been paid (round to the nearest percentage point)? Redo the problem if the interest rate is 2.00% (for a well designed spreadsheet this should take 30 seconds) e) What is the annual payment? f) What are the total interest payments? g) After 22 payments have been made, what percentage of the total interest has been paid (round to the nearest percentage point)? h) After 22 payments have been made, what percentage of the total principal has been paid (round to the nearest percentage point)