Today, John borrowed $40,000 from the bank. His initial plan is to pay his entire principal and interest amount in 48 months. He plans to pay some extra payment for the first year to pay off the debt earlier. As an excel programming trainer, please do the following requirements:
a. If He plans to make extra payment of $600 every month. Write a flexible program that returns the correct loan amortization schedule (including month, interest, payment, repayment of principal and remaining balance) and the correct output section for any change in items in input section.
b. You need to roundup the actual loan period sine partial period does not make sense. You have to use the if statement and the program should be flexible with respect to all variables in the input section .
c. John needs to find out when it is the first time the remaining balance hit below $10,000, so he may exercise the option to prepay off the rest of the loan. You need to use the excel function such as Vlookup , Index, Match, or IF to solve this question. You are allowed to create additional column to assist your problem solvoing. But you can not manually find out the answer, you still need to make the solution flexible.
d. In a separate excel tab, solve for the following question: If the extra payment $600 starts from month 1 till the end of month 12, please redo the question, find out the actual loan payment, and the first time your balance hit below $10,000.