Help with Goal seek : trying to find a rate

[lindatea]

help.jpg
Hi,

I am given the data set to solve, I am trying to find a interest rate % based on the number of variables that I know.

How do i find out the interest % if we only know the principal amount and the number of days? I know total discounted amount in column N, but I would not know the data in column L.

RED highlighted is the data I would like to calculate (seek) or find the way to get this number from the data set that I have. I have tried using Goal Seek but no success.

Note: the yellow highlighted are the data set that depends on the RED cell.

Sorry I should also add that the reason I know the discount amount is $27,961,632 (the original data would not have column L M and N at all), we only know the total discounted is $27,961,632.

Thank you.
Linda

[MrShorty]

We might need to know something about what you have already tried, so we don't end up repeating something that you already know doesn't work. We also probably need to know exactly what solution you expect to get for this problem to make it easier to explore algorithms and tools to get to that solution (assuming at this point that there is only one solution to the problem). Here's what I did:

1) Set E36 to 0 and ran goal seek (target cell E45 to a value of 0 by changing E36). Goal seek wobbled all over the place and was generally unstable.
2) Set E36 to 0 and ran Solver (target cell E45 to a value of 0 by changing E36). Solver reported no solution, but the value in E45 was between 1 and -1. Repeating with different starting values in E36, and Solver never claimed to find a solution, but its solutions were generally close.
3) Set up a data table (result cell E45, row input cell E36 https://www.excel-easy.com/examples/data-tables.html ) and then created a scatter chart to explore the behavior of the objective function. As I honed in on the solution, I discovered that the objective function becomes a step function near the solution. Algorithms like Newton's method (used by Goal Seek and the default algorithm in Solver) have real trouble with step functions. It also appears that the objective function will never be exactly 0, so, ultimately, you've probably got to come up with a "close enough" threshold that you will accept for a solution.

Looking forward, I see a couple of different possibilities.

A) Probably the easiest is to use Solver instead of Goal seek (intro to solver if you are not yet familiar: https://www.mrexcel.com/excel-tips/i...ion-to-solver/ ). You will want to tinker with the different solution algorithms to see which algorithm ends up being most reliable (evolutionary might be best). You might want to change the objective function (E45) to something an absolute value function or similar so you can find the minimum value (closest to 0) rather than the exact value of 0. Maybe spend some time with your objective function until you are more familiar with its behavior. With enough time in those kinds of endeavors, I expect you should be able to find a way to get Solver to reliably find your solution.
B) Build your own solution algorithm. I would probably base it on the bisection method rather than Newton's method. With enough effort, I expect we could come up with a bisection algorithm that will find the two steps that bracket 0, then decide what to do for the final solution from there.

I hope I haven't repeated what you have already tried, but that looks to me like the next steps in developing the solution.

[curiouscat408]

@lindatea.... I don't understand or I don't agree with your financial math.

But there is nothing wrong with using Goal Seek (or Solver) that removing ROUND in column L cannot fix.

ROUND is ill-advised when using Goal Seek and the GRG Nonlinear method in Solver because it creates "discontinuities" in the interative derivation.

Sometimes, we get lucky, and these algorithms seem to work. But generally, such functions cause oscillations, as we see in the behavior of Goal Seek, and/or missed solutions. The same is true for other "discontinuous" functions like MIN, MAX, ABS, lookups, etc.

When we remove ROUND, Goal Seek sets E36 to 3.82573044500135% (+ 4.86E-17), with a difference in E45 of about -1.06E-07.

Solver sets E36 to 3.82573044500517% (+ 1.39E-17), with -6.43E-05 in E45, when we set the All Methods Constraint and GRG Nonlinear Convergence to 1E-12.

(I typically choose such precision arbitrarily.)

The original value in E36 is 3.82573044378044% (+ 4.16E-17), with 2.05E-02 in E45, which rounds to 0.02(!). That's a significant difference compared to the Goal Seek and Solver results, which I can only speculate about.

When we restore ROUND with those results in E36, the difference in E45 is about -6.56E-02 (rounds to -0.07) for the Goal Seek and Solver, and about -3.89E-02 (rounds to -0.04) for the original value.

Arguably, we might play with those results manually to get a less-significant difference. For example, 3.82573044325436% (optionally up to + 5.55E-17, which is 8*2^-57) results in a difference of about 1.67E-02 (rounds to 0.02) in E45. I believe that is the best that we can do within the limits of 64-bit binary floating-point (*).


-----
(*) "The best that we can do" unless there are multiple points where the formulas might cause E45 to cross zero. I did not bother to look.

[lindatea]

Hi,

Sorry I am not being clear on my question, I think I have fried my brain last night.

To be clear, below is what required of me to perform.

Target : Need to find the rate in E36

Known information:
- All highlighted are the data set
- E36 is being used to calculate data in Column L and Column N
- therefore I would not know the number in Column N if there we could not calculate E36, but I know the total sum of Column would be $-27,961,632 because we know the sum of E (E32) +3,084,124 should equal to 27,961,632.

Thank you
Linda

[lindatea]

Attachment 788406

Hi,

I have tried using Goal seek for the running changing E36 by setting E43 to 0 or set E46 to -3,084,124, none return the rate % that is the same as the RED highlighted.

I might have a look at Solver

Thank you

[curiouscat408]

Attachment 788406
I have tried using Goal seek for the running changing E36 by setting E43 to 0

Cannot read the attachment.

Sorry I was not clear. I thought the goal is setting E45 to zero by changing E36, thus ensuring that the sum in E43 is the same as the expected value in E44.

Thus, E36 becomes the discount rate that is used in column L.

PS.... It makes no difference if you use Solver. The goal and "by changing" are the same. The only advantage of Solver is: we can control the precision of the iterative algorithm.

But as I demonstrated, ironically, the Goal Seek result is better than the Solver result for the unrounded formulas, and the result is exactly the same for the rounded formulas.

BTW, I might not have been clear: change the formulas in L6:L29 from (in L6, for example) =-ROUND(F6*G6/365*$E$36,2) to =-F6*G6/365*$E$36, at least when using Goal Seek or Solver.

You can restore the ROUND, if you wish. But since the discounted cash flows (column N) are not rounded, I see no reason to round the interest amounts.

And we can never expect the unrounded result in E45 to be exactly zero, if only because binary arithmetic anomalies. (Long story. Put a pin in it.)

Moreover, as demonstrate, even the rounded result cannot be exactly zero for those cash flows.

[lindatea]

sorry curiouscat408 that i wasnt clear, thought i have click reply means it will replied to you.

"Sorry I was not clear. I thought the goal is setting E45 to zero by changing E36, thus ensuring that the sum in E43 is the same as the expected value in E44.

Thus, E36 becomes the discount rate that is used in column L."

Could you please show me how to use goal seek to derived E36 (the discounted rate?) as that is what I am trying to achieve, but no matter what value I use I could not get E36 value top 3.827304%.

Thank you curiouscat408

[lindatea]

[QUOTE=lindatea;5701903]sorry curiouscat408 that i wasnt clear, thought i have click reply means it will replied to you.

"Sorry I was not clear. I thought the goal is setting E45 to zero by changing E36, thus ensuring that the sum in E43 is the same as the expected value in E44.

Thus, E36 becomes the discount rate that is used in column L."

sorry I meant to quote this and ask if you know the way to set goal seek to get E36 discount rate? I couldnt get this rate with the information.

[curiouscat408]

Could you please show me how to use goal seek to derived E36 (the discounted rate?) as that is what I am trying to achieve, but no matter what value I use I could not get E36 value top 3.827304%.

What do mean by "top 3.827304%"? Is "top" a typo, and you mean "to"?

And is 3.827304% [sic] a typo? Do you really mean 3.82757304%?

There is nothing wrong with 3.82757304%, as long as you rounded it for conversation, and the actual value has more precision.

But when we are talking about interest/discount rates, precision can be important. It is, in this case.

So it is best to always show numbers with 15 significant digits to avoid miscommunication and confusion.

-----

See the attached image and Excel file for instructions.


goal seek problem.jpg


Aside.... Can you read that image attachment above? I am still wondering why I cannot read yours; and I worry that you cannot read mine for the same reason.

I copied the original values for E36 and E43:E45 into column H for comparison. How was that value of E36 derived?

Follow the instructions in the textbox at I35.

Note that I already changed the formulas in L6:L29. They were of the form =-ROUND(F6*G6/365*$E$36,2). Now they are of the form =-F6*G6/365*$E$36.

As I explained, we should not use ROUND in formulas that are changed by the Goal Seek (or Solver) algorithm and that affect the target cell directly or indirectly.

The image shows the result that I get. If you get something different, show us what you get. Don't just talk about it in vague terms.

The derived discount rate in E36 is 3.82573044500135%. Those digits to the right are important.

The value that we get in E43 is -3,084,124.00000016, an infinitesimal difference from the expected value in E44.

Thus, their difference in E45 appears to be -0.00000016. It is actually -0.000000160187482833862. Again, an infinitesimal difference from zero.

-----

Does this help?

Or are you saying that 3.82573044500135% in E36 is not acceptable for some reason?

If so, please explain what you expect and why.

[Alf]

Hi curiouscat408

Can you read that image attachment above? I am still wondering why I cannot read yours; and I worry that you cannot read mine for the same reason.

You image read fine to me but there often is a problem when adding images in this forum. After posting check you post and if you see your image everything is OK but is you see "Atachment XXXXX" then nobody can see it. In this case edit your post and remove the attachment then add it again. Second time round ursually fixes this proble.

A working solver setup could perhaps look like this. But first you need to remove the "ROUND" function as solver can't work with it (commented by curiouscat408). Choose instead a setting with 2 decimals.

solv_setup.jpg

Alf

[curiouscat408]

there often is a problem when adding images in this forum.

FDibbins, forum admin, writes in PM: ``The "insert image/file" does not work. You need to use the "Manage Attachments" option.``

[Alf]

The "insert image/file" does not work. You need to use the "Manage Attachments" option.

Thanks, good to know I've been adding the same image twice to get it working.

Alf

[curiouscat408]

I've been adding the same image twice to get it working.

Interesting. Odd that it would work a second time if it doesn't work the first time. Odd that it worked one time for lindatea, but not another time. (Presumably she didn't know about your trick.)

The advantage of Insert Image is: we can choose where the image is placed. For many images, it's nice to put it near the discussion of it.

I don't see a way to do that with Manage Attachments.

So it's worth it to try you trick. My concern is: __my__ perception of the image might not be how others perceive it. In other words, the fact that __I__ can view the image is not dispositive.

I worry that it might work for me because I am working on the same computer where the image file originated, and/or I might have sufficient privilege (or cookies) to access the file, whereas others might not.

But if it works for you (i.e. it fails the first time, but it works the second time), hopefully it will work the same way for me.

And maybe that's why the forum does not disable the feature altogether.

-----
@lindatea (if you're still participating), the take-away is: I wish you would edit your posting #5 and "replace" the attachment, either by retrying Insert Image or at least by using Manage Attachments (follow directions in the yellow banner at the top), if that's a copy of the assignment or details instructions.

It might help me understand your financial math -- or what you are trying to do.

[Alf]

nteresting. Odd that it would work a second time if it doesn't work the first time. Odd that it worked one time for lindatea, but not another time.

When I added the image in post 10 I had to do it twice before I could see the image in my post. Neither can I explain this behavior. I do use jpg format for posting as years ago I and other members could not see png format some browsers had problem with that so I got into the habit of using jpg format.

Conclusion: This Forum works in mysterious ways its miracles to preform.

Alf

[curiouscat408]

I do use jpg format for posting as years ago I and other members could not see png format [because] some browsers had problem with that

The reason that I always use JPG is because it is less than half the size of PNG. I have never seen any degradation due the "lossy" nature of the JPG compression.