the answer is .. It depends on the frequency of compounding of interest and if you deposit at beginning of the period or end of the period
per
https://www.ablebits.com/office-addins-blog/2019/04/17/excel-pmt-function-formula-examples/#PMT-function-in-Excel
"The Excel PMT function is a financial function that calculates the payment for a loan based on a constant interest rate, the number of periods and the loan amount"
PMT(rate,nper,pv,fv,type)
so with excel you type in a cell "=PMT"( followed by a number, a comma, a number, a comma,a number, a comma,a number, a comma, a number, a comma and ")"
I will assume interest compounds monthly and payment is at beginning of month.
if interest compounds monthly to determine rate, you take the annual rate 0.05 and divide by 12 (the number of times in the year the compounding happens)
nper is number of periods, so for 5 years and monthly compounding it is 5*12 periods or 60 periods
pv is the present value of your investment, zero since you start off with nothing
fv is the future value of your investment, if you will receive 20,000 you enter a positive number 20,000 as fv
type is 1 (payment at beginning of period) or 0 (payment at end of period)
if you do not specify type, Excel assumes you want 0 (payment at end of period)
so the answer in excel is calculated with =pmt(0.05/12,5*12,0,20000,1) [I will assume interest compounds monthly and payment is at beginning of month.]
($292.87)
excel returns a negative number, that means you are paying to the bank that amount
If I assume interest compounds monthly and payment is at beginning of month, the payment is 292.87
if you assume interest compounds monthly and payment is at end of month, the answer is 294.09 ( formula is =PMT(0.05/12,5*12,0,20000,0)
($294.09)
You always have a greater payment if payment is at the end of month, because if you pay at beginning of month your money "compounds faster"
to see how you could use basic math lets look at a simpler scenario, say 5% annual interest compounded monthly, payments at end of period, you want to have 20,000 at end
Excel =PMT(0.05/12,3,0,20000,0) answer 6,638.97 payments
Now how to get same answer using basic math:
build a table with 6 columns: column A period, column B value at beginning, column C rate, column D = column B x (1+column C), column E payment at end, column F balance end of period
row 1:
column A 1
Column B 0
Column C 0.004166667
Column D =column B * (1+column C) =0
Column E 6,638.97
Column F balance at end = column D + column E = 6,638.97
row 2:
column A 2
Column B balance form previous row
Column C 0.004166667
Column D =column B * (1+column C)
Column E 6,638.97
Column F balance at end = column D + column E = 13,305.59
row 3:
column A 3
Column B balance form previous row
Column C 0.004166667
Column D =column B * (1+column C)
Column E 6,638.97
Column F balance at end = column D + column E = 20,000
for payments at beginning of period, the columns in the table are slightly different
column A period (1,2,3,…)
Column B starting balance
Column C payment (at beginning of period)
Column D sum of previous two columns
Column E calc new balance (previous column*(1+0.05/12)
