Menghitung Rumus Fungsi NPER & PMT dalam Microsoft Excel

Summary

This transcript explains financial functions used to calculate loan payments and investment periods in Excel. It focuses on key formulas like NPER and PMT, which help determine the number of payment periods and monthly payments based on fixed interest rates. Through two examples, one involving a loan of 100 million Rupiah with a 12% interest rate and another with a 20-year repayment period, the script illustrates how to apply these formulas to solve real-world financial problems. The explanations highlight the importance of input parameters like interest rate, loan amount, and payment type in obtaining accurate results.

Takeaways

• 😀 MPR is a financial function used to calculate the number of periods needed for installment payments or investments with a fixed interest rate.
• 😀 The MPR formula is written as: =NPR(rate, PMT, PV, FV, type), where rate is the interest rate, PMT is the installment payment, PV is the present value, FV is the future value, and type specifies when payments are due (0 for end of period, 1 for beginning).
• 😀 The interest rate (rate) is expressed as a percentage, while PMT represents the fixed monthly payment amount.
• 😀 The negative sign in PMT indicates that it's an outgoing payment, while positive numbers indicate an incoming amount, according to the financial function convention.
• 😀 In the example, Tuan Ahmad borrows 100 million IDR and repays 1.2 million IDR each month. The interest rate is 12% annually. The total repayment period is calculated to be 180 months.
• 😀 NPR is used to calculate how many months it will take to repay a loan based on the monthly payment, loan amount, and interest rate.
• 😀 Another example involves Tuan Budi, who borrows 100 million IDR from a bank at a 12% annual interest rate for 20 years (240 months).
• 😀 For the second example, the monthly installment is calculated by inputting the formula with the appropriate rate, number of periods, loan amount, and future value set to zero.
• 😀 The formula used in these examples is an application of standard financial calculations to determine loan terms and repayment structures.
• 😀 Understanding how to use the financial functions like MPR and NPR is crucial for accurate loan planning and financial forecasting.

Q & A

What is the purpose of the MPR function in financial calculations?

The MPR function is used to calculate the number of periods required to repay a loan or investment given a fixed interest rate, payment amount, and principal value.

How do you use the MPR function in a spreadsheet?

To use the MPR function, input the following formula: `=MPR(rate, PMT, PV, FV, type)`. The parameters include rate (interest rate per period), PMT (payment per period), PV (present value or principal), FV (future value, usually set to 0), and type (0 for end of period, 1 for start).

Why are the payment amounts in the MPR and PMT functions written as negative values?

Payment amounts are written as negative values in financial functions because the negative sign indicates money being paid out (an outflow), while positive values indicate money being received (an inflow).

What does the PMT function calculate in financial contexts?

The PMT function calculates the fixed monthly payment amount required to repay a loan or investment over a set number of periods, given a fixed interest rate and principal value.

How is the interest rate applied in the MPR and PMT functions?

The interest rate is applied as a percentage per period. If the annual rate is provided, it should be divided by the number of periods per year (e.g., 12 for monthly payments) to get the rate per period.

What is the significance of the 'type' parameter in both the MPR and PMT functions?

The 'type' parameter determines when payments are made during each period: 0 for payments at the end of the period, and 1 for payments at the beginning of the period.

How do you calculate the number of months required to repay a loan of 100 million Rupiah with a 12% annual interest rate and a monthly payment of 1.2 million Rupiah?

To calculate the number of months, you can use the formula: `=MPR(12%/12, -1200000, 100000000, 0, 0)`, which will return the result of 180 months (15 years).

What is the formula for calculating monthly payments for a loan of 100 million Rupiah at a 12% annual interest rate over 20 years?

To calculate the monthly payment, use the formula: `=PMT(12%/12, 240, 100000000, 0, 0)`. The result will be approximately 1,000,101 Rupiah.

What do the parameters 'FV' and 'PV' represent in financial functions like MPR and PMT?

In financial functions, 'PV' represents the present value or loan amount, while 'FV' represents the future value, which is typically set to 0 in loan calculations (indicating the loan will be fully paid off).

Why is it important to differentiate between when payments are made (beginning vs. end of the period) in these functions?

Differentiating the payment timing (beginning or end) is crucial because it affects the total interest paid over the life of the loan. Payments made at the beginning of the period typically reduce interest costs compared to those made at the end.