General task – create a loan calculator, showing two alternative variants of repayment schedule.
Assumptions
• The loan is repaid in monthly installments.
• The maximum loan period is 30 years.
• The repayment schedule should show:
o ordinal number of each installment,
o subsequent (monthly) installments broken down into interest and principal repayment,
o remaining liability (before subsequent repayments are made).
o Totals (e.g. total interest paid on the loan)
• Two variants of repayment schedule should be presented alongside – (a) decreasing installments, (b) fixed installments.
Parameters (user input)
• interest rate (separate for the two schedules)
• frequency of payments per year (1, 2, 4, 12)
• number of installments (given frequency)
• loan value
• postponement period (of the first installment)
Layout and behavior
• The table showing the repayment schedule should dynamically change its size depending on the number of installments. It should include only as many rows as the number of
installments.
• Subsequent lines should have alternating white and shaded background. Formatting should be visible only for the rows filled with values.
• Summaries (totals) should be shown above the repayment schedule (not below!).
• Use mechanisms to protect those parts of the worksheet which should not be edited by the user .
• You should hide unused areas of the sheet.
• For each category of data / results you should be apply appropriate formatting.
• The worksheet should include mechanisms (validation) to prevent unacceptable parameter values (e.g. negative interest rate) .
• Form controls (e.g. spin button, list) should be used to facilitate the user’s choice of parameters.
Financial functions used in the exercise: PMT, PPMT, IPMT.
Other functions: ISODD, ISEVEN.
