Grace Jordan graduated from the University College of Barbados with a BSc in Accounting and Finance with 1^st Class Honors in 2014. Shortly thereafter she was employed by the Cherry Tree Co-operative Credit Union Limited in Barbados. In 2017, Grace and her two (2) sisters purchased a condominium for $300,000 and Grace obtained a loan for $260,000 from her employer.

At the time the mortgage was taken out, the term of the mortgage loan was for twenty-five (25) years and the concessionary rate provided by the Credit Union to employees, such as Grace, was 0.75% below the prevailing mortgage rate of 5.85% per annum to existing customers of the Credit Union. The amount of the monthly mortgage payment was calculated based on the full amount of the mortgage, the interest rate applicable, and constant fixed mortgage payments over the full term of the mortgage loan. The mortgage payments commenced on May 1, 2017.

Grace was advised, but nevertheless new, that the mortgage payment is made on the 1^st of each month, and that the interest amount is calculated on a daily basis and based on the number of days in a particular month. Effective September 1, 2020, the Credit Union reduced the prevailing mortgage rate to existing customers of the Credit Union to 5.35%, with the concessionary rate to employees being still 0.75% below.

In January 2022, Grace applied to Graduate School and received confirmation to attend the Berlin School of Finance and Management in Germany, starting in January 2023. The overall cost of the program at the Berlin School of Finance and Management is €42,250 and the financing arrangements agreed by Grace is that the fees will be paid in three (3) equal installments on September 12, 2022, October 10, 2022, and November 7, 2022. Grace’s commercial bank have advised her that the payment requests can be facilitated, however the international payment application must be initiated two (2) business days before the agreed payment dates to ensure that the Euros reach the Berlin School’s bank on the days noted.

Grace’s grandparents, who live in Canada, agreed to a gift of CND$105,000 and 15,000 shares of Goddard Enterprises Limited to be divided equally between Grace and her two (2) sisters. The Canadian funds were received by the Barbados Bank on September 27, 2019, and Grace’s share of the Barbados Dollar equivalent was deposited into her account on that day. Grace’s portion of the Goddard Enterprises Limited shares were deposited into her account at the Barbados Central Depository Securities on October 1, 2019.

Grace, knowing that she was considering attending Graduate School, agreed to invest her share of the cash gift from her grandparents. She decided to invest 90% of the amount received (rounded to the nearest $100) equally between the Sagicor Global Balanced Fund and the Fortress Caribbean Growth Fund, and these investments were made on October 7, 2019.

Following the decision to attend Graduate School, Grace resigned her position from the Credit Union effective October 31, 2022, having given two (2) months’ notice. The Mortgage Department of the Credit Union have advised Grace, and her sisters, that the concessionary rate on the Mortgage would revert to the prevailing rate from November 1, 2022, with all other terms of the mortgage remaining unchanged. (This rate will be provided on November 1, 2022)

Grace decided to liquidate the entire holdings in the Sagicor Global Balanced Fund and the Fortress Caribbean Growth Fund to assist in paying the fees to the Graduate School. These mutual funds were liquidated on November 4, 2022, to meet the 3^rd Graduate School fee payment on November 7, 2022.

Note, for this assignment, you will need to obtain historical and current exchange rate information and historical and current information on securities

Question

Prepare a mortgage schedule in Microsoft Excel covering the period from May 1, 2017, to October 31, 2022. The mortgage schedule should show, at a minimum, interest paid by the month and principal paid by the month, total mortgage payment by month, and the balance outstanding at the end of each month.