Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert A Formula to Value Until A Value is Met
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert A Formula to Value Until A Value is Met
On Jul 26, 4:27*pm, cardan wrote:
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." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert vlookup formula to link formula | Excel Worksheet Functions | |||
How to convert a static formula to dynamic formula ? | Excel Worksheet Functions | |||
Excell convert formula row to formula column | Excel Worksheet Functions | |||
how to convert a formula into text in order to display the formula | Excel Discussion (Misc queries) | |||
Convert Normal formula to array formula | Excel Programming |