Practice 9: Financial Functions & PMT Calculations

Key Topics & Core Concepts

Periodic Payment Syntax: Mastering the PMT(rate, nper, pv, [fv], [type]) syntax.
Rate & Period Conversion: Dividing annual percentage rates by 12 and multiplying loan duration in years by 12 for monthly compounding.
Cash Outlay & Interest Modeling: Computing total lifetime payments and deriving cumulative interest paid over the life of a loan.
Sign Convention in Financial Formulas: Applying negation to Present Value (-pv) to ensure calculated payments display as positive cash outflows.

Assignment Overview & Deliverables

In this practice lab, you will construct a commercial equipment loan model for Perfect Vacation Rentals, computing monthly installment payments, total cash outlay, and net interest expenses across multiple financing terms.

Starter Workbook: Download PerfectRentals-04.xlsx
Completed File Name: [your initials] Practice09_PMT.xlsx
Active Worksheets: Financing Model and Summary

Step-by-Step Instructions

Step 1: Open Starter & Review Financing Parameters

Download and open PerfectRentals-04.xlsx in Excel.
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice09_PMT.xlsx.
Click the Financing Model worksheet tab.
Review the loan parameters in cells B4:B6:
- Cell B4 (Purchase Price / Present Value): $68,000
- Cell B5 (Annual Interest Rate): 6.25%
- Cell B6 (Term in Years): 5

Step 2: Calculate Base Monthly Payment with PMT

Click cell B8 (Monthly Payment).
Type: =PMT(B5/12, B6*12, -B4) and press Enter.
Confirm that dividing B5 by 12 yields the monthly interest rate, multiplying B6 by 12 provides total payment periods, and -B4 ensures a positive result.

Step 3: Calculate Total Outlay & Lifetime Interest

Click cell B9 (Total Amount Paid).
Type: =B8*(B6*12) and press Enter.
Click cell B10 (Total Interest Paid).
Type: =B9-B4 and press Enter.

Step 4: Build Comparative Multi-Term Schedule

In row 13, set up term columns across C13:E13 for 3 Years, 5 Years, and 7 Years.
In cell C14, enter a PMT formula referencing the 3-year duration with absolute references locking the rate ($B$5) and loan principal ($B$4): =PMT($B$5/12, C13*12, -$B$4).
Copy the formula across through cell E14 to compare monthly payment requirements across different loan durations.
In rows 15 and 16, compute the corresponding Total Outlay and Total Interest for each term option.

Step 5: Apply Number Formatting & Save Workbook

Format all monetary values as Currency ($#,##0.00).
Format interest rate cells as Percentage with two decimal places (0.00%).
Auto-fit columns to prevent cell overflow (###) errors.
Save your completed workbook.

Practice exercises are formative and prepare you for homework submissions. Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Skill Area	Verification Target
PMT Function Syntax	`=PMT(rate/12, nper*12, -pv)` correctly configured with monthly rate adjustments.
Cash Outflow Modeling	Total payment and cumulative interest formulas properly reference monthly payment outputs.
Comparative Loan Analysis	Absolute locks ($) correctly structured to copy PMT formulas across multiple loan terms.
Financial Formatting	Consistent currency formatting and clean presentation without cell truncation errors.