Goal seek with help of VBA

elmacay

Board Regular
Joined
May 4, 2006
Messages
88
Hi guys and girls,

In order to impress my CFO, who can't figure it out, I put myself in your hands, since I can't figure it out either, but he doesn't know that... :wink:

Here's what I want to do:

We have built a building for let's say 1,000,000. We want to know what the rent is that we have to ask in order to make the 1,000,000 as well, earn it back.
So I need the interest rate (5%), the number of years in which I want to earn it back (10), and the yearly increase in rent (2,5%).

I have made a table saying
year 1: rent / (1+interest rate) = net present value (NPV)
year 2: (rent + increase) / (1+interest rate)^2 = NPV
and so on for the next 8 years.

So I made me a nice little VBA

Code:
Range("D21").GoalSeek Goal:=1000000, ChangingCell:=Range("B5")
where B5 is the rent I have to charge.

Now here's the question, coz this is too limited
Can I automate the goal seeking thing where all things are variable? I want to be able to change the years, the interest rate and the increase in rent, which in the end comes back with the rent I have to charge.

I can't figure it out. Can anyone of you wizzards?

Greetz, Elmacay
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi
Paste the following codes in the macro window ( alt F11)

Code:
Sub Macro2()
Cells(1, 1) = "Rent"
Cells(1, 2) = "annual" & Chr(10) & "increase"
Cells(1, 3) = "Interest"
Cells(1, 4) = "Target"
For a = 1 To 8
rent = Cells(2, 1)
AI = Cells(2, 2)
Interest = Cells(2, 3)
Cells(2, 4) = Cells(2, 4) + rent + (a - 1) * AI / 100 / (1 + Interest / 100) ^ 2
Next a
End Sub
Fill cellsA2,B2 and C2 and run the macro. D2 gives cumulative value. you can vary the values and run the macro each time till you achieve the goal (goal seek does not work because target is not from a simple formula. it needs to loop for 8 years. -simply put, I don't know a formula which can do that) You can assign the macro to an object on the sheet to make it convenient to change values and observe its effect on the target value.
RAvi
 
Upvote 0

Forum statistics

Threads
1,214,262
Messages
6,118,550
Members
448,835
Latest member
Profast123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top