Excel Ch02: Townsend Mortgage Company Instructions

Exp22 Excel Ch02 Hoe Townsend Mortgage Company Instructions

You are an assistant to Yui Matheson, a mortgage broker at the Townsend Mortgage Company. Yui spends days reviewing mortgage rates and trends, meeting with clients, and preparing paperwork. Yui relies on your expertise in using Excel to help analyze mortgage data. Today, Yui provided you with sample mortgage data: loan number, house cost, down payment, mortgage rate, and the length of the loan in years. She asked you to perform some basic calculations so that the output provided by her system can be checked to verify that it is calculating results correctly.

You have also been tasked with calculating the amount financed, the periodic interest rate, the total number of payment periods, the percentage of the house cost financed, and the payoff year for each loan. In addition, you will calculate totals, averages, and other basic statistics. Furthermore, Yui asked you to complete another worksheet that uses functions to look up interest rates from a separate table, calculate the monthly payments, and determine how much (if any) the borrower will have to pay for private mortgage insurance (PMI).

Paper For Above instruction

In this report, I will demonstrate the comprehensive use of Excel functions and formulas to analyze mortgage data for Townsend Mortgage Company, aligning with the tasks outlined by Yui Matheson. The primary aim is to verify mortgage calculations through formulas and statistical functions and to enhance data analysis through lookup functions, advanced formulas, and automation features such as conditional statements and date functions.

To initiate the process, the dataset provided includes essential mortgage details such as loan number, house cost, down payment, mortgage rate, and loan term in years. The first step involves calculating the amount financed for each mortgage. This is achieved by subtracting the down payment from the house cost, formulated as =C2-D2 in cell D8, and then copying this formula down the entire column. This calculation offers a clear view of the principal amount borrowed for each loan, which is fundamental for further calculations.

Secondly, the calculation of the monthly interest rate per loan is essential for amortization and payment calculations. Using the mortgage rate in column E and the number of payment periods per year (assumed as 12), the formula in F8 becomes =(E8/100)/B$5, where B$5 contains the number of payments per year. This absolute cell reference allows consistency across all calculations, and copying the formula downward ensures all loans’ monthly interest rates are correctly computed.

Next, determining the total number of payment periods involves considering the loan term in years. Using a mixed reference, the formula in H8 is =B8*$B$5, where B8 contains the loan duration in years, and B$5 holds the number of payment periods per year. Copying this formula down offers a comprehensive view of each loan’s total payments over its lifespan.

Additional statistical functions include calculating total, average, median, minimum, maximum, and count for the home costs. The AutoSum feature in cells B16 and B17 computes the total and average house costs, respectively. The median in B18, minimum in B19, maximum in B20, and total count in B21 employ appropriate functions such as =MEDIAN(B8:B15), =MIN(B8:B15), =MAX(B8:B15), and =COUNTA(B8:B15). These statistical summaries facilitate understanding of the mortgage dataset’s distribution and central tendencies.

By editing a value, for instance changing cell B9 to 425,000, the formulas automatically update, allowing for what-if analyses and sensitivity testing. The insertion of the current date via the TODAY() function in B4 ensures real-time tracking of data or report generation date.

The core of mortgage analysis involves the use of lookup functions. The XLOOKUP function retrieves the annual percentage rate (APR) from a rate table based on the number of years financed, located in D4:E6, with the lookup value in F9. Using an absolute reference for the table ensures accurate and consistent data retrieval across calculations.

Subsequently, the PMT function calculates monthly payments based on the retrieved APR, the loan term, and the amount financed. Proper absolute referencing ensures fixed input variables during the copying process. The formula in H9 is =PMT(G9/12, F9*B$5, -D9), with G9 representing the APR, F9 the years, and D9 the amount financed.

Lastly, the calculation of private mortgage insurance (PMI) is conditional. Using an IF function in I9, the formula checks if the down payment percentage is less than 20% (cell B7). If true, the monthly PMI is calculated as 0.0038 times the amount financed; otherwise, it is zero. The formula is =IF(B7

This comprehensive Excel-based analysis enables Townsend Mortgage Company to verify their mortgage calculations, perform statistical summaries, and assess the impact of different parameters on loan payments and costs. The integration of lookup functions, statistical functions, and conditional formulas streamlines the mortgage analysis process, providing accurate and actionable insights.

References

  • Excel Skills for Business: Essentials, 2nd Edition by Macmillan Publishers.
  • Walkenbach, J. (2013). Excel 2013 Bible. Wiley Publishing.
  • Harper, R. (2018). Mastering Excel Formulas and Functions. Wiley.
  • Microsoft Support Documentation. (2023). Lookup functions in Excel. https://support.microsoft.com.
  • Rizzo, J. (2019). Financial Analysis with Microsoft Excel. Pearson.
  • Higgins, R. (2020). Quantitative Methods for Business. Pearson.
  • Sharma, R. (2021). Data Analysis and Business Modeling with Excel. Springer.
  • Simons, P., & Korth, B. (2022). Advanced Excel Techniques for Financial Analysis. Wiley.
  • Financial Modeling in Excel For Dummies by Danielle Stein Fairhurst, 3rd Edition.
  • Keller, S. (2017). Practical Financial Modelling with Excel. Routledge.

Related Assignments