Most of us like to invest part of our incomes on a periodic basis. And we base that on a financial goal of accumulating certain lumpsum at the end of our investment endeavor. So, we have a total amount in mind, and the number of periods we would like to invest. Is there a way to calculate the constant and periodic payment amount that leads to the total amount we just spoke about? Fortunately, we have the PMT formula in Google Sheets that can do it for us.
PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])
- rate – is the rate of interest.
- number_of_periods – is the number of periods that the buyer wishes to make payment for.
- present_value – is the current value of the annuity.
- future_value – [ OPTIONAL ] – is the amount of the future value that remains after we make the final payment.
- end_or_beginning – [ OPTIONAL – 0 by default ] – a 0 indicates that we are making the payments at the end of each period. And a value of 1 specifies that we are making payments at the beginning of each payment period.
Usage: PMT formula in Google Sheets
Now that we learned the concept, let us apply our newfound knowledge directly on the Google Sheets application. Because examples make our learning process easier and faster. Please consider the screenshot below.
On the first formula (row # 2), we calculated the periodic payment using only the first three parameters while ignoring the last two. It calculated the payment amount that the investor has to pay for 60 periods to reach a lump sum of 10000 at 1.25% interest rate.
Using the second formula, we calculated the periodic payment amount towards a targeted amount. Which is equivalent to 10000 in present value plus 4000 in future value (after 60 periods). Which is why the payment value is higher than that of the first formula.
The first and the third formulas look essentially the same, but with different output values. Because there is one fundamental difference. In the first formula, we ignored the final parameter. Therefore, we inherently indicated that we are making the payment at the end of the period. Whereas in the last formula, we specified that we are paying at the beginning of the payment period.