Key Topics & Core Concepts

Assignment Overview & Deliverables

In this project, you will build a 4-year undergraduate college budget projection model and a student loan repayment calculator. Follow the step-by-step instructions below to enter base costs, apply inflation assumptions, cross-link sheets, and compute loan terms.

Step-by-Step Instructions

Step 1: Open Workbook & Inspect Setup

  1. Click the download button above to save CollegeCost_Starter.xlsx to your computer.
  2. Open the workbook in Excel. If a yellow bar appears at the top, click Enable Editing.
  3. Click File > Save As > Browse. Navigate to your course folder and save the workbook as [your initials] Homework04_CollegeCost.xlsx (for example: JD_Homework04_CollegeCost.xlsx).
  4. Look at the bottom of the Excel window and verify you see two sheet tabs: Costs and Growth. Click the Costs tab to begin.

Step 2: Calculate Year 1 Base Expenses (Costs Sheet)

  1. On the Costs sheet, inspect the Year 1 expenses in cells B3:B6:
    • Cell B3 (Tuition & Fees): $12,500
    • Cell B4 (Housing & Food): $8,400
    • Cell B5 (Books & Supplies): $1,200
    • Cell B6 (Personal / Transportation): $1,800
  2. Click cell B7 (Year 1 Total Expenses).
  3. Type =SUM(B3:B6) and press Enter. Verify the result displays $23,900.

Step 3: Apply Annual Inflation with Absolute References ($)

  1. Notice cell B1 contains the annual estimated inflation rate (e.g., 3.5% or 0.035). You must use an absolute reference ($B$1) so this cell does not shift when dragging formulas.
  2. Click cell C3 (Year 2 Tuition).
  3. Type =B3*(1+$B$1) and press Enter. (This increases Year 1 tuition by the inflation rate).
  4. Click back on cell C3. Move your mouse over the small green square in the bottom-right corner of the cell until your cursor turns into a thin black cross (+).
  5. Click and drag the fill handle from cell C3 down to cell C6 to calculate Year 2 Housing, Books, and Personal costs.
  6. While cells C3:C6 are selected, grab the fill handle again and drag across columns D (Year 3) and E (Year 4).
  7. Click cell B7 (Year 1 Total). Grab its fill handle and drag across to cells C7:E7 to total each remaining academic year.

Step 4: Calculate Cumulative 4-Year Totals & Net Costs

  1. Click cell F3 (4-Year Total Tuition).
  2. Type =SUM(B3:E3) and press Enter.
  3. Click cell F3 and drag the fill handle down through cell F7. Cell F7 represents your total gross educational expenses over 4 years.
  4. Click cell B10 (Scholarships & Grants). Enter your expected annual gift aid (e.g., $5,000). In cells C10:E10, copy or enter the annual award amounts.
  5. Click cell F10. Type =SUM(B10:E10) and press Enter.
  6. Click cell B12 (Net Out-of-Pocket Cost). Type =B7-B10 and press Enter. Drag the fill handle across through cell F12.

Step 5: Cross-Sheet Links & Statistical Analysis (Growth Sheet)

  1. Click the Growth tab at the bottom of Excel.
  2. Click cell B3 (Year 1 Tuition link). Type =Costs!B3 and press Enter.
  3. Drag the fill handle from cell B3 across to E3 to pull in Tuition for all 4 years directly from the Costs sheet.
  4. Click cell C4 (Tuition Dollar Change, Year 1 to Year 2). Type =C3-B3 and press Enter.
  5. Click cell C5 (Tuition % Change). Type =(C3-B3)/B3 and press Enter. Format cell C5 as Percentage with 1 decimal place (0.0%).
  6. Repeat or drag the formulas across to columns D and E to compute the year-over-year dollar and percentage growth for Years 3 and 4.
  7. In the Summary Statistics block below:
    • Click the cell for Average Tuition: type =AVERAGE(B3:E3) and press Enter.
    • Click the cell for Maximum Annual Cost: type =MAX(Costs!B7:E7) and press Enter.
    • Click the cell for Minimum Annual Cost: type =MIN(Costs!B7:E7) and press Enter.

Step 6: Model Student Loan Payment with PMT

  1. Return to the Costs sheet. Scroll to the Loan Repayment Model section (starting around row 16).
  2. Review the loan parameters:
    • Cell B16 (Principal Borrowed): Type =F12 (links to total net 4-year cost).
    • Cell B17 (Annual Interest Rate): Enter 6.5% (or 0.065).
    • Cell B18 (Loan Term in Years): Enter 10.
  3. Click cell B20 (Monthly Payment).
  4. Type the following formula: =PMT(B17/12, B18*12, -B16) and press Enter.

    Note: We divide the rate by 12 for monthly interest, multiply years by 12 for total monthly periods, and use -B16 so the monthly payment displays as a positive currency amount.

  5. Click cell B21 (Total Payments). Type =B20*(B18*12) and press Enter.
  6. Click cell B22 (Total Interest Paid). Type =B21-B16 and press Enter.

Step 7: Format, Self-Check & Canvas Submission

  1. Select all monetary cells across both sheets and format as Currency ($#,##0 or $#,##0.00) via the Home tab > Number drop-down.
  2. Select rate and percentage change cells and format as Percentage (0.0%).
  3. Perform a self-check: Change the inflation rate in cell B1 to 5.0%. Verify that all expense totals, growth stats, and loan payments dynamically update. Then set cell B1 back to 3.5%.
  4. Save your workbook: press Ctrl + S (or Cmd + S).
  5. Submit your finalized [your initials] Homework04_CollegeCost.xlsx file to Canvas under the Homework 4: College Cost Analysis assignment.

Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Skill Area Verification Target
Basic Aggregation =SUM(B3:B6) and annual four-year totals correctly computed.
Absolute Coordinate Lock $B$1 correctly applied to compound inflation across years 2, 3, and 4.
Cross-Sheet Linking =Costs!B3 syntax used on Growth tab without duplicate data entry.
Financial Function (PMT) =PMT(rate/12, nper*12, -pv) correctly configured with monthly rate and period adjustments.
Descriptive Statistics AVERAGE, MAX, and MIN formulas properly reference target expense ranges.