Convert A Formula to Value Until A Value is Met

[cardan]

Hello, I have somewhat of a complex problem with a circular
reference. I am in need of a macro that will convert a formula to a
value.

PROBLEM: I am working on a financial model where Interest is
calculated on Total Costs. Total Costs include Interest, and therefore
I have a circular reference. My model is fairly large (cash flows
monthly over 10 years) so this pretty much crashes my computer. These
Total Costs are paid by an "Investor" and a "Lender". This split is
decided on % of Total Costs. (ie Lender will pay 80% of the costs)

I would like to have a macro that will take the Investor and Lender
values computed within the model, convert them to Values and then I
can use those newly converted values as assumptions to start the model
to break the circular reference link.

Whenever the Total Costs change, there will be a difference between
the Total Costs used as the assumptions and what the Total Costs
calculated in the cash flow. This means I have to change the
assumptions, which means the Total Costs will change again , etc....
The good thing about this scenario is that the numbers will eventually
converge until they reach zero or something very close.

I would like to add to this macro the ability for it to keep
calculating through until the difference between assumptions used in
the model and the calculated total costs is between a range of -1 and
1.

Is this possible? Any help or feedback would be tremendously
helpful. Thank you for your time.

 

[Don Guillett Excel MVP]

Hello, I have somewhat of a complex problem with a circular
reference.  I am in need of a macro that will convert a formula to a
value.

PROBLEM:   I am working on a financial model where Interest is
calculated on Total Costs. Total Costs include Interest, and therefore
I have a circular reference. My model is fairly large (cash flows
monthly over 10 years) so this pretty much crashes my computer. These
Total Costs are paid by an "Investor" and a "Lender".  This split is
decided on % of Total Costs. (ie Lender will pay 80% of the costs)

I would like to have a macro that will take the Investor and Lender
values computed within the model, convert them to Values and then I
can use those newly converted values as assumptions to start the model
to break the circular reference link.

Whenever the Total Costs change, there will be a difference between
the Total Costs used as the assumptions and what the Total Costs
calculated in the cash flow.  This means I have to change the
assumptions, which means the Total Costs will change again , etc....
The good thing about this scenario is that the numbers will eventually
converge until they reach zero or something very close.

 I would like to add to this macro the ability for it to keep
calculating through until the difference between assumptions used in
the model and the calculated total costs is between a range of -1 and
1.

Is this possible?  Any help or feedback would be tremendously
helpful.  Thank you for your time.

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."