Note that since the payments are monthly, the interest is taken as 5%/12. Here is the formula that will calculate it: You can also use the PMT function to calculate how much you should invest per month to get a certain amount in the future.įor example, suppose you want to invest in a way to get USD 100,000 in 10 years when the annual interest rate is 5%. Example 2 – Monthly Payment to Grow Your Investment to USD 100,000 If you want it to be positive, make the loan amount negative.Īlso, remember that the interest rate remains constant throughout the period. Note that the loan payment is negative as it’s a cash outflow. The monthly payment of the loan has to be calculated. Let’s assume that a student loan of 100,000 is to be paid off completely in 10 years with a 4 annual interest rate. You can omit the optional arguments as these are not needed.īelow is the formula that will calculate the loan payment amount using the PMT function: By default, the excel function considered that the payments are made at the end of the period. The PMT function returns payment for the loan as a negative value to represent the cash outflow, such as Figure 4.