ı want to solve this question by excel formulas?

You initially put $5,000 into the bank at 4% annual interest rate. Each year you put in an additional $2000. After 20 years of doing this, you start taking out bank disbursements (basically payments *from* the bank) to pay for college. If you will take out 4 equally spaced disbursements to pay for 4 years of college and the final value of the college loan will be 0 after taking those 4 disbursements, what will be the value of each of those 4 disbursements?

[Sam Chak]

Alright, @matlab coder. Can you show the mathematical formulas that you used in MS Excel?

We will then see if there is way to import/transfer the Excel formulas to MATLAB.

[matlab coder]

This is what ı have done. But actually ı dont want its formula for matlab. I couldnt find its result and ı want to see if anyone could help for excel solution. Because there is no platform that ı know to discuss about excel problems, ı am new on excel and ı want to improve myself about it. I just thought that maybe someone from here could be capable of solving such a problem. Thanks for your respond.

[Sam Chak]

Hi @matlab coder

First year, you deposit $5,000.

For the next 19 years, you top-up $2,000 each year. (Please clarify whether 19 years or 20 years). It affects @Torsten's formula.

Then, you send your children to college and pay the tuition fees for 4 years.

What does 4 equally spaced disbursements mean? Does it mean the total amount divided by 4?

Say, Year 1 in College (or 21st year), you stop putting money. After making a disbursement to pay the annual tuition fee, the balance continues to grow at 4% annual interest rate.

[Walter Roberson]

This is not a question about MATLAB and should not have asked here.

Microsoft has its own forums. "In Q2 FY 2022, productivity and business processes generated $15.9 billion in revenue, comprising 31% of Microsoft's total revenue." Notice that is one quarter alone. The cost of supporting questions about Excel (with no matlab context) should be on Microsoft.

[Torsten]

As far as I understand the question, there is software under Excel for this kind of problem (PMT)and the OP wants to extract the relevant formula from Excel for use in MATLAB.

[Walter Roberson]

User explicitly wants excel... "But actually ı dont want its formula for matlab. I couldnt find its result and ı want to see if anyone could help for excel solution. Because there is no platform that ı know to discuss about excel problems, ı am new on excel and ı want to improve myself about it."

[Torsten]

Sorry - I only read the title " I want to solve this question by Excel formulas ".

Answer 1

[Torsten]

Without guarantee:

disbursement = (5000*1.04^23 + 2000*1.04^4*(1.04^19-1)/0.04)/((1.04^4-1)/0.04)

thus approximately 18150 $.

[Sam Chak]

@matlab coder

You can try computing a solution for the financial problem in MATLAB, and then compare the solution with the result you got in MS Excel. By the way, I'm not familiar with the PMT function in Excel. Need some time to study, or perhaps you can give a brief explanation.

https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441

I'm amazed with @Torsten who swiftly came up with the formula. So, please make good use of that and modify it if necessary.

[Torsten]

Start year 1: 5000 
After 1 year: 5000*1.04 + 2000
After 2 years: 5000*1.04^2 + 2000*1.04 + 2000
...
After 20 years: 5000*1.04^20 + 2000*(1.04+1.04^2+...+1.04^19) 

(Don't know if after 20 years, 2000 $ are still put in - I assumed no).

Beginning of 21st year: 
5000*1.04^20 + 2000*(1.04+1.04^2+...+1.04^19) - dbm
Beginning of 22nd year: 
5000*1.04^21 + 2000*(1.04^2+1.04^2+...+1.04^20) - dbm*1.04 - dbm
Beginning of 23rd year: 
5000*1.04^22 + 2000*(1.04^3+1.04^3+...+1.04^21) - dbm*1.04^2 - dbm*1.04 - dbm
Beginning of 24th year: 
5000*1.04^23 + 2000*(1.04^4+1.04^4+...+1.04^22) - dbm*1.04^3 - dbm*1.04^2 - dbm*1.04 - dbm =
5000*1.04^23 + 2000*1.04^4*(1.04^19-1)/0.04 - dbm*((1.04^4-1)/0.04) == 0

Solve for dbm.

[Sam Chak]

I think @Torsten is correct. (+1)

The compound interest after 20 years is $65,876.70. Then

Year 21: $65876.70*1.04 - x

Year 22: ($65876.70*1.04 - x)*1.04 - x

Year 23: (($65876.70*1.04 - x)*1.04 - x)*1.04 - x

Year 24: ((($65876.70*1.04 - x)*1.04 - x)*1.04 - x)*1.04 - x ... the 4th disbursement empties the account

format longg
f = @(x) (((65876.70*1.04 - x)*1.04 - x)*1.04 - x)*1.04 - x;
dbm = fsolve(f, 20000)

dbm = 18148.37506946083