Goal seek with help of VBA

[elmacay]

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

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

 

[ravishankar]

Hi
Paste the following codes in the macro window ( alt F11)

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