Microsoft® Excel Spreadsheet Exercise #1

How to use this section: Each exercise requires the development of an Excel spreadsheet. The exercises are keyed to sections in the text Engineering Economy, 5th edition, by Blank and Tarquin. Appendix A of the text is a complete primer for using Excel and the financial functions pertinent to engineering economic analysis. Sample problems are included in this appendix for setting up each function.

The spreadsheet exercises presented here are especially well suited to an engineering economy course with laboratory sessions or activities that help a student become more adept with spreadsheet-based solutions.

Spreadsheet Exercise #1

An Introduction to Spreadsheet Modeling: Loan Repayment

Exercise Objective: Introduce you to spreadsheet modeling and analysis. At the completion of this exercise, you should be able to:

• Construct a basic Excel worksheet.
• Assemble an Excel workbook that contains a series of charts and worksheets
• Create mathematical equations
• Use copy, paste, and fill functions
• Comprehend the use of absolute and relative references
• Format worksheets
• Use Excel functions
• Create simple charts

Blank and Tarquin Text Reference: Chapter 1 (specifically Example 1.9) and Appendix A.

Problem Statement: Jane has applied for a $30,000 loan to be repaid over six years at the stated interest rate of 8% per year.

Part 1: Loan Repayment Schedules
Create an Excel workbook to model and evaluate five different repayment schedules as explained below. Each schedule should be prepared on a different worksheet. Name each worksheet with the appropriate title.

1. Lump sum plan with simple interest (entire principal and simple interest paid at the end of the loan period)
2. Lump sum plan with compound interest (entire principal and compound interest paid at the end of the loan period)
3. Constant principal pay down (accrued compound interest and constant principal repayment are paid each year)
4. Interest plan - simple interest (accrued simple interest paid yearly; entire principal repaid at the end of the loan period)
5. Fixed interest - fixed payment plan (equal end-of-year covering partial principal repayment and accrued compound interest for the year)

Part 2: Excel Chart
Prepare a line chart that depicts the end-of-year balance on each repayment schedule. This chart should have 5 lines, 1 for each schedule. The y-axis is $ and the x-axis is years 0-5.

Part 3: Fixed interest - fixed payment loans
For the "fixed interest - fixed payment" repayment schedule, create a "stacked column" chart that displays for each year the portion of the loan payment that is principal and interest.