SOLVER Question (d)

[onewishtobegranted]

Hi guys, I have already done parts a-c but it is part d that I've been stuck at, not sure if it is possible to use solver to get my answers because of the unknown constraints.

Please help thank u... And I can't upload the file since it contains my answer and my school doesn't allow us to post them... thanks.

Screenshot 2020-08-28 at 6.41.27 PM.png

[AliGW]

We can't help you unless you are prepared to tell us and/or show us what you have tried. We do not hand you solutions on a plate - that's not ethical when this is meant to be homework.

Tell us what thoughts you have had about how to solve the problem.

[onewishtobegranted]

Hi,

First of all, I would like to thank you. I'm sorry if I seem like I am fishing for answers.

I have tried to use solver to compute the answer for (d) but it doesn't seem like it is realistic, and also I would like to know if there is a way for solver save the situations where I change my constraints? Since I always Restore to Original Values and create an Answer Report.

Attached is what I have currently come up with.

Attachment 692885 < still not sure of how to upload

[AliGW]

Please update the thread title in post #1 to make it clear that this is a solver query.

[MrShorty]

Without a better understanding of your spreadsheet and Solver model (and an understanding of the financial principles), it is difficult to know what a realistic answer is and debug the situation.

One thing to remember, Solver will find a mathematically correct solution. The way the question is worded suggests that there could be many (infinite?) possible mathematically correct solutions. In a case like this, you may need to include in your Solver model constraints on different variables to "realistic" values so that Solver will return a solution within "realistic" ranges. Not knowing any of the specifics, I cannot make any specific suggestions.

If I assume that the spreadsheet you built for part a is built on combinations of the PV() and/or FV() functions (or other functions within the basic present value/future value family of financial functions), I wonder if it would be possible to build part d using RATE() or other combinations of the basic PV() family of functions. Ignoring the complication of dual compounding rates, part a is solving the "given present value, compounding rate, number of periods, and periodic payment amount, compute future value" problem. Part d is asking you to solve the "given present value, and future value, periodic payment, and number of periods, find the compounding rate" problem. Again ignoring the dual compounding rates, a RATE() function would readily solve that problem.

[onewishtobegranted]

Hi MrShorty,

Thank you so much for the suggestion! After much thinking, I realised that using RATE() might actually work. I will try it out.

I have one small question, if im investing diff amounts every year, what is the pmt actually for?

[MrShorty]

I'm not sure I understand the question, but that could be because I know very little about these financial models.

You have not shared any real detail about the financial model you are using, I am still assuming some kind of simple present value model. I would have expected that while programming part a of the problem, you would have figured out how to combine the different compounding rates into your financial model. Part d is about understanding what you did in part a well enough to "invert" the process. If you used a present value model, I would expect that the process includes a step where you combine all of the compounding rates into a single effective compounding rate and fixed payment. Or maybe you used a net present value kind of model -- which has its own rate function in Excel, the IRR() function.

I don't know how to do part d, because you have not explained how you did part a. The solution to part d is to understand what you did in part a well enough to come up with a suitable "inverse" of the calculations done in part a.

[onewishtobegranted]

My part (a) is a model made up of

Income: I multiply the previous years' income*income increment (3%)
Savings: which is the 10% of my Income, therefore, I take the year's income*savings goal (10%)
Bank Interest: which is what I earn every year from putting money in the bank, therefore, savings*bank interest

Since I've decided to invest 20% of my savings into bonds and stocks,
Remaining Savings: is the remaining 80% I have left of my savings after investing.

Investment value: the 20% I decide to invest,
Investment earnings: investment value*investment return rate.

Thus, I have a total bank balance=bank interest+remaining savings+investment earnings.

I'm not very sure if I got the model correct for part a, therefore not rly sure of how to move on with part d.

I hope this gives u more explanation on how I get my total bank balance.

[MrShorty]

I'm still not sure I understand what you currently have. You attempted to upload your sample file in post #3; perhaps try again to upload your file being sure to follow the procedure outlined in the yellow banner.

Am I to understand that your current programming strategy is to compute each year's result in the spreadsheet? Kind of an amortization table? Or are you using PV() or NPV() or XNPV() or similar functions to compute multiple years at once?

[onewishtobegranted]

hi, i'm not sure what u mean... i'm very new to all the terms in excel and what they do, but i've attached the workbook below.

to calculate the total value, i just used a SUM function, idk what NPV actually does tho

[MrShorty]

If you don't know anything about the NPV() function, then I am guessing you are not expected to use it here. I will leave it to you to research net present value (or wait until you coursework brings it up).

I would agree that your solution to part a seems incorrect. In particular, the investment balance does not seem to be growing as fast as it should. If I calculate the future value of the initial 4200 that you put into investment at 3.5% compounded annually assuming no additional money added to the investment for 14 years [=FV(E18,14,0,K28)], I get about $6800, but your model only shows $5300 after 14 years. It seems like the investment balance is not growing as fast as it should -- especially if you are also contributing additional funds every year.

Perhaps I am misunderstanding the kind of financial models you are trying to use to estimate how your savings grows, but it seems like it is not growing as fast as it should -- which could be why your Solver solutions return unreasonable results when you get to problem d. Since I don't understand exactly what your financial model should be, I cannot suggest specific changes to your sheet.

[onewishtobegranted]

hi mr shorty, it's ok! thank you for ur time though!

[onewishtobegranted]

hi i've solved my question, is it possible to delete thread?