Thomas Bata is in the process of buying an apartment in the city of Kosice (Slovakia) and takes out a fully amortized ?400,000 mortgage that has to be repaid over the next 10 years in 40 equal quarterly installments (the first installment is due 3 months from today). Since he wants to pay off the mortgage sooner than scheduled, he pays an additional ?4,400 above the required amount on each of the payments he will make (starting with the first payment). Also, he makes a one-time special payment of EUR 7,500 at the end of year 2, and another one at the end of year 4. This means that he pays an additional EUR 7,500 together with (i.e., on top of) the 8th and the 16th regular payments. Set up a spreadsheet-based amortization schedule for this ?400,000 loan which you develop from scratch. Interest rate = 4.25% p.a., quarterly compounded Minimum requirements: - Show for all periods until the mortgage is completely paid off a) loan amount at begin of period, b) total payment, c) interest paid, d) repayment of principal, e) loan amount at end of period. - Make sure you adjust the last mortgage payment so that the mortgage is exactly paid off (no negative mortgage balance, no empty rows, etc.) - Show total of all payments, total interest paid, total repayment of principal - Show all the numbers in ?, 2 decimal places.

Spreadsheet problem based on chapter 4 (TVM) Part One: Mortgage Thomas Bata is in the process of buying an apartment in the city of Kosice (Slovakia) and takes out a fully amortized 400,000 mortgage that has to be repaid over the next 10 years in 40 equal quarterly installments (the first installment is due 3 months from today). Since he wants to pay off the mortgage sooner than scheduled, he pays an additional 4,400 above the required amount on each of the payments he will make (starting with the first payment). Also, he makes a one-time special payment of EUR 7,500 at the end of year 2, and another one at the end of year 4. This means that he pays an additional EUR 7,500 together with (i.e., on top of) the 8th and the 16th regular payments. Set up a spreadsheet-based amortization schedule for this 400,000 loan which you develop from scratch. Interest rate = 4.25% p.a., quarterly compounded Minimum requirements: - Show for all periods until the mortgage is completely paid off a) loan amount at begin of period, b) total payment, c) interest paid, d) repayment of principal, e) loan amount at end of period. - Make sure you adjust the last mortgage payment so that the mortgage is exactly paid off (no negative mortgage balance, no empty rows, etc.) - Show total of all payments, total interest paid, total repayment of principal - Show all the numbers in , 2 decimal places. Project assignment: - Create a one-page spreadsheet solution that includes all the required parameters - Convert the excel-file to a pdf-file