Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

Sample Data

| Loan Amount | Interest Rate | Loan Term | Start Date  |

|-------------|---------------|-----------|-------------|

| 10000       | 5%            | 3 years   | 2023-01-01  |

 

Task :

1. Calculate the monthly payment using PMT function based on Loan Amount, Interest Rate, and Loan Term.
2. Create an amortization schedule to show monthly payments, principal paid, interest paid, and remaining balance.
3. Use DATE function to calculate the end date of the loan.
4. Insert a line chart to visualize the trend of remaining balance over the loan term.
5. Use Goal Seek feature to determine the maximum loan amount based on a specified monthly payment.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

1. Calculate Monthly Payment Using PMT Function:
   • Use the PMT function in Excel to calculate the monthly payment for the loan.
   • Example: =PMT(interest_rate/12, loan_term_in_years*12, -loan_amount) assuming loan amount is in cell B2, interest rate is in cell B3 (5% per annum), and loan term is in cell B4 (3 years).
2. Create an Amortization Schedule:
   • Set up columns for Month, Payment, Principal Paid, Interest Paid, and Remaining Balance.
   • Calculate each month's values based on the PMT result and adjust the remaining balance accordingly using Excel formulas.
3. Use DATE Function to Calculate Loan End Date:
   • Use the DATE function to calculate the end date of the loan based on the start date and loan term.
   • Example: =DATE(YEAR(start_date) + loan_term_years, MONTH(start_date), DAY(start_date)) assuming start date is in cell B5 and loan term is in cell B4.
4. Insert a Line Chart to Visualize Remaining Balance Trend:
   • Select the data range of the remaining balance over time.
   • Go to Insert > Line Chart > Line (or Smooth Line) to create a chart showing the trend of remaining balance over the loan term.
   • Customize the chart with appropriate titles, axis labels, and legend.
5. Use Goal Seek Feature to Determine Maximum Loan Amount:
   • Set up a scenario where you want to determine the maximum loan amount based on a specified monthly payment.
   • Use the Goal Seek feature (under Data > What-If Analysis > Goal Seek) to find the maximum loan amount that allows you to achieve the desired monthly payment.
   • Specify the target cell (monthly payment cell), the desired value (monthly payment amount), and the changing cell (loan amount cell).

Detailed Steps:

• Step 1: Calculate Monthly Payment Using PMT Function
  • Enter the formula =PMT(B3/12, B4*12, -B2) in a cell (assuming loan amount is in B2, interest rate is in B3, and loan term in years is in B4).
• Step 2: Create Amortization Schedule
  • Set up columns for Month (1, 2, 3,...), Payment (from Step 1), Principal Paid, Interest Paid, and Remaining Balance.
  • Calculate Principal Paid each month using =PPMT
  • Calculate Interest Paid each month using =IPMT
  • Calculate Remaining Balance each month by subtracting Principal Paid from Previous Month's Remaining Balance.
• Step 3: Use DATE Function for Loan End Date
  • Enter the formula =DATE(YEAR(B5) + B4, MONTH(B5), DAY(B5)) in a cell (assuming start date is in B5 and loan term in years is in B4).
• Step 4: Insert Line Chart for Remaining Balance Trend
  • Select the range of data including Months and Remaining Balance.
  • Go to Insert > Line Chart > Line (or Smooth Line) to create the chart.
  • Customize chart elements (title, axis labels) as necessary.
• Step 5: Use Goal Seek to Determine Maximum Loan Amount
  • Set up a cell for the desired monthly payment (target cell).
  • Use Goal Seek to find the maximum loan amount that results in the specified monthly payment.