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)