Circular Reference

[Rfinkle]

I am having problem with a formula that references itself. Here is what I am trying to do. i will put in the numbers that i have marked known and i would like to generate purchase price, mortage payment and cash flow. Purchase price references itself. I can do the calculation on paper but excel does not like it. Thanks for the help.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Income
[/TD]
[TD]pulled in from another cell
[/TD]
[/TR]
[TR]
[TD]Expenses
[/TD]
[TD]pulled in from another cell
[/TD]
[/TR]
[TR]
[TD]Mortgage Type
[/TD]
[TD]text block
[/TD]
[/TR]
[TR]
[TD]Interest Rate %
[/TD]
[TD]known value
[/TD]
[/TR]
[TR]
[TD]Downpayment %
[/TD]
[TD]known value
[/TD]
[/TR]
[TR]
[TD]Mortgage (Years)
[/TD]
[TD]known value
[/TD]
[/TR]
[TR]
[TD]Mortgage Payment
[/TD]
[TD]=-pmt(interest rate/12, mortgage years*12, P)
[/TD]
[/TR]
[TR]
[TD]ROI %
[/TD]
[TD]known value
[/TD]
[/TR]
[TR]
[TD]Purchase Price =P
[/TD]
[TD]P=((Income-Expenses)-((Income-Expenses)-c))/ROI
[/TD]
[/TR]
[TR]
[TD]Cash Flow=c
[/TD]
[TD]c=ROI*P
[/TD]
[/TR]
</tbody>[/TABLE]

 

[cyrilbrd]

Hi,
May I know what cell does "pmt" make reference to?

 

[Rfinkle]

pmt is a function that refers to "mortgage payment". It calculates mortgage payment with the above values given. Thanks!

 

[Rfinkle]

Formulas Referencing their own self???

I want to have an equation that references itself...ie P=(x-y)-(z*P) where P is unknown and x,y,z are known. I understand that you could solve for P in the equation. In this instance I do not want to do that. Anyone have any tips?

 

[Joe4]

Re: Formulas Referencing their own self???

I have merged your two threads together. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: </SPAN>http://www.mrexcel.com/forum/showthread.php?t=99490</SPAN>).

I understand that you could solve for P in the equation. In this instance I do not want to do that.

What is your aversion to solving for P and using that as your equation?
Do you just need helping figuring out how to do that?
The formula would just be: P=(x-y)/(z+1)

 

[Rfinkle]

Re: Formulas Referencing their own self???

Take a look at the formula on the first post of the thread. The P=(x-y)-(z*P) was just an example. The one from the first thread when you solve for P=P. But when you use real numbers you can come to an answer that works. P=((Income-Expenses)-((Income-Expenses)-c))/ROI where c=ROI*P

Hope this makes sense

 

[Joe4]

Re: Formulas Referencing their own self???

I am a bit confused. If you don't know what the Purchase Price is (that is what you are trying to calculate), how can you have it as one of the variables in your formula?

In your original post, you say that you can do it "on paper", but jkust having issues making it work in Excel. Can you walk us through a real example (step-by-step) using numbers of how you do it "on paper", and maybe we can help you translate that into Excel?

 

[Rfinkle]

Re: Formulas Referencing their own self???

I do not have my work with me. I tried to do a similar calculation and it is not working. When i get home tonight i will post my work. In the meantime, is there a function to calculate principal of a mortgage knowing the interest rate, years and payment?

 

[Joe4]

Re: Formulas Referencing their own self???

Take a look here: http://www.ehow.com/how_2154016_calculate-principal-payment-using-microsoft.html

 

[pgc01]

Re: Formulas Referencing their own self???

Hi

Have ou already checked the help? Excel has more than 50 financial functions some of them dealing with the type of problem you have.