Construct the amortization of the loan for the twenty years in a spreadsheet to show the interest cost, the principal reduction, and the ending balance each month. Complete the amortization schedule below to show the interest cost. the principal reduction, and the ending balance for month 125 of the loan: (Round to the nearest cent.) Years for Loan Amount Interest Rate Loan \( \$ 650,000 \) Beginning Monthly Interest Principal Extra Ending

To complete the amortization schedule for **month 125** of your loan, you'll need the following information: 1. **Loan Amount**: \$650,000 2. **Loan Term**: 20 years (240 months) 3. **Interest Rate**: *[This information is missing and is essential for the calculation]* Since the **interest rate** is not provided, I'll explain how to construct the amortization schedule and calculate the required values assuming an example interest rate. You can replace the example rate with your actual rate once you have it. ### **Assumed Details for Demonstration** - **Loan Amount (Principal)**: \$650,000 - **Loan Term**: 20 years (240 months) - **Annual Interest Rate**: **5%** *(You should replace this with your actual rate)* ### **Step-by-Step Guide** 1. **Calculate Monthly Interest Rate** \[ \text{Monthly Interest Rate} = \frac{\text{Annual Interest Rate}}{12} = \frac{5\%}{12} = 0.4167\% \text{ or } 0.004167 \] 2. **Determine Monthly Payment** The monthly payment can be calculated using the **PMT** function in spreadsheets like Excel or Google Sheets: \[ \text{Monthly Payment} = \text{PMT}(\text{Monthly Interest Rate}, \text{Total Number of Payments}, -\text{Loan Amount}) \] Plugging in the values: \[ \text{Monthly Payment} = \text{PMT}(0.004167, 240, -650000) \approx \$4,301.33 \] 3. **Amortization Schedule Components** Each month's payment is divided into: - **Interest Cost**: \( \text{Beginning Balance} \times \text{Monthly Interest Rate} \) - **Principal Reduction**: \( \text{Monthly Payment} - \text{Interest Cost} \) - **Ending Balance**: \( \text{Beginning Balance} - \text{Principal Reduction} \) 4. **Calculating for Month 125** To find the values for month 125, we'll need to iterate through each month up to the 125th month. However, to simplify, we can use amortization formulas or spreadsheet functions to find the required values without manually calculating each month's balance. **Using Excel or Google Sheets:** - **Beginning Balance for Month 125**: You can use the **FV** (Future Value) function to calculate the remaining balance before the 125th payment. \[ \text{Beginning Balance} = \text{FV}(\text{Monthly Interest Rate}, 124, \text{Monthly Payment}, -\text{Loan Amount}) \] \[ \text{Beginning Balance} = \text{FV}(0.004167, 124, 4301.33, -650000) \approx \$X \] *(Replace \$X with the actual calculated value)* - **Interest Cost for Month 125**: \[ \text{Interest Cost} = \text{Beginning Balance} \times \text{Monthly Interest Rate} \] - **Principal Reduction for Month 125**: \[ \text{Principal Reduction} = \text{Monthly Payment} - \text{Interest Cost} \] - **Ending Balance for Month 125**: \[ \text{Ending Balance} = \text{Beginning Balance} - \text{Principal Reduction} \] ### **Example Calculation for Month 125** Assuming the beginning balance before month 125 is \$250,000 (for demonstration purposes): 1. **Interest Cost**: \[ \text{Interest Cost} = 250,000 \times 0.004167 = \$1,041.75 \] 2. **Principal Reduction**: \[ \text{Principal Reduction} = 4,301.33 - 1,041.75 = \$3,259.58 \] 3. **Ending Balance**: \[ \text{Ending Balance} = 250,000 - 3,259.58 = \$246,740.42 \] ### **Final Amortization Details for Month 125** | Beginning Balance | Monthly Payment | Interest Cost | Principal Reduction | Ending Balance | |--------------------|-----------------|---------------|---------------------|-----------------| | \$250,000.00 | \$4,301.33 | \$1,041.75 | \$3,259.58 | \$246,740.42 | *Note: The above numbers are illustrative. Replace the beginning balance with the actual balance calculated up to month 124 using the FV function or an amortization table.* ### **Creating the Amortization Schedule in a Spreadsheet** 1. **Set Up Columns**: | A | B | C | D | E | F | |---|-------------|--------------|---------------------|---------|----------------| | 1 | Month | Beginning | Interest Cost | Principal Reduction | Extra Payment | Ending Balance | 2. **Input Initial Values**: - **Month 1**: - **Beginning Balance**: \$650,000.00 - **Interest Cost**: =A2 * Monthly Interest Rate - **Principal Reduction**: =Monthly Payment - Interest Cost - **Ending Balance**: =Beginning Balance - Principal Reduction 3. **Drag Formulas Down**: - Apply the formulas to subsequent months up to month 240. 4. **Locate Month 125**: - Scroll down to the 125th row to find the required details. ### **Conclusion** To accurately complete the amortization schedule for month 125: 1. **Obtain the Exact Annual Interest Rate** for your loan. 2. **Use Spreadsheet Functions** like PMT and FV to automate calculations. 3. **Ensure All Formulas Are Correctly Applied** to maintain accuracy throughout the schedule. If you provide the actual interest rate, I can help you perform the exact calculations for month 125.