Help with excel formula

[PhishHead]

I know alot of you guys out there are smart math people I am trying to create a formula in excel for the life of lease payments but I just can't seem to do it.

Basically its this:

A Lease has a rent payment of $10,000 a year
Every 5 Years there is an increase of 5%
The lease is 30 years long.

I can do this easily by the long way but it takes up too much space.
I know there is a formula I just cant think of it.

I also would like one where the Years are able to be changed according to the variable in the problem, thus if its 10,000 a year and the increase is 10% every 2 years I can change it that way too.

I am just not sure of the variable formula.

Any help would be greatly appreciated.

thanks

[JPMNICK]

edit: forget it i just reread the problem

[PhishHead]

The part i meant to add in after all the facts are given is:

I need a formula that shows the payments for the life of the loan, not all the payments just that amount that has been paid through the 30 years.

if done long way would look like this:
1 10,000
2 10,000
3 10,000
4 10,000
5 10,000
6 10,500
7 10,500
8 10,500
9 10,500
10 10,500
11 11,025
12 11,025
13 11,025
14 11,025
15 11,025
16 11,576
17 11,576
18 11,576
19 11,576
20 11,576
21 12,155
22 12,155
23 12,155
24 12,155
25 12,155
26 12,763
27 12,763
28 12,763
29 12,763
30 12,763
340,096

I want a formula that gets that last number (340,096) .

[JPMNICK]

would it be ok for this to be done using a Visual Basic Module in Excel?

[PhishHead]

would it be ok for this to be done using a Visual Basic Module in Excel?

if you mean like a macro, preferably no, but if thats the only way to do then hell yes!!

I thought of using the macro as well just wasnt sure how to write that either.

[JPMNICK]

if you mean like a macro, preferably no, but if thats the only way to do then hell yes!!

I thought of using the macro as well just wasnt sure how to write that either.

I think i might be able to do it with no macro, which is what I am trying first

[JPMNICK]

does it make sense to do 2 calculations here?

one find out the total compounded interest rate, and then use that to do the total calculation?

[PhishHead]

does it make sense to do 2 calculations here?

one find out the total compounded interest rate, and then use that to do the total calculation?

that seems like it would work too.

I found this online:
(2*((ir + 1)^yr - 1)*s)/ir

in my search for a formula, the person doesn't go into great detail about it but:
ir = interest rate
yr equals years but doesn't explain what years, the years of increase or years of the lease
s = starting amount of payments.
This was for a lease that had changes every 2 years in rent

I tried to plug my numbers into it but was coming up with the wrong stuff every time. Thats why I asked here.

But your idea of two separate calculations seems like it would work.

[JPMNICK]

yea the hard part for you is that the payment only changes for certain years. i think calculating the total interest rate, and then applying that number into one of the more normal formulas might help

[Bob Impact]

edit:nm, figured it out, you either need to do multiple cells or a VBA function, but you cant have it all in one formula that I can see.

[JPMNICK]

I think you might be able to have it in one function. instead of calculating the interest based on quarterly, which is x/4 (where x represents 1 year) i think we can do something like 5/x. i will test it now and see

[Bob Impact]

I dont know much about Finance, but Excel has 1000 Financial formulas, one of them has to work, or a combination of two.

[PhishHead]

bob thats what i was thinking too i just couldnt find one that fit it, but i was just looking at one that encompassed everything not two separate ones.

I would also need a formula where the increases are every year at a constant percentage, so every year a 3% increase for example. That would seem like the easy one though. I am just not great at math. and i knew Nick and a few others on the board were amazing.

[JPMNICK]

A = P (1+i/12)^(12t)

that is for monthly interest
P = Principal
I = interest rate
T = time in years

if we change it to be something like this:

A = P (1+5/i)^(t/5)

this is not working 100% yet, but it is on the way. I have a 3pm work meeting, I will look at it more later

[JPMNICK]

bob thats what i was thinking too i just couldnt find one that fit it, but i was just looking at one that encompassed everything not two separate ones.

I would also need a formula where the increases are every year at a constant percentage, so every year a 3% increase for example. That would seem like the easy one though. I am just not great at math. and i knew Nick and a few others on the board were amazing.

use the 1st formula from above for the constant increase

just remove the divided by 12 part unless it is monthly


OR


In excel you can use the future value function

FV(rate,nper,pmt,pv,type)

Rate is the interest rate per period.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period; it cannot change over the life of the annuity. If pmt is omitted, you must include the pv argument.

Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.

Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
For example, suppose that you will invest $1,000 today at an interest rate of 12 percent, and you would like to know what the investment will be worth at the end of five years.

The FV formula is entered as follows:
=FV(12%,5,0,-1000,0)

[nate1000]

Here is a formula that works:

P=Payment ($) - $10,000 from your example
N=number of constant payments before increase- 5 from your example
Y= years in lease - 30 from your example
R= rate of increase - 5% from your example

total # of payments=

N(P) [( ∑(1+R)^i)+1]

Where the summation is i=1 to (Y/N-1)

in the notation above- remember the hat sign (^) raises the function to the ith power.

So it is:

5 ($10,000) [ (∑(1+.05)^i)+1]

i=1 to (30/5-1) or 5

then:
$50,000 (5.8019 +1)
$50,000 (6.8019)= $340,096


Good luck shoehorning that into an excel cell. You could build a template table for yourself so you can just plug in the inputs.

[LordJezo]

Go post that same question here:

http://www.mrexcel.com/board2/index.php

The excel general help place is a pretty intense forum for all things like this.

[zentraed]

Here is a formula that works:

P=Payment ($) - $10,000 from your example
N=number of constant payments before increase- 5 from your example
Y= years in lease - 30 from your example
R= rate of increase - 5% from your example

total # of payments=

N(P) [( ∑(1+R)^i)+1]

Where the summation is i=1 to (Y/N-1)

in the notation above- remember the hat sign (^) raises the function to the ith power.

So it is:

5 ($10,000) [ (∑(1+.05)^i)+1]

i=1 to (30/5-1) or 5

then:
$50,000 (5.8019 +1)
$50,000 (6.8019)= $340,096


Good luck shoehorning that into an excel cell. You could build a template table for yourself so you can just plug in the inputs.

Good work with this one. I didn't double check it, but I did write it as a single line formula.

∑(1+R)^i can be easily written as [(1+R)^(n+1) - 1]/R - 1, so your Excel formula would be:

N*P*(((1+R)^(Y/N)-1)/R)

Using your values (except with R=.05), that formula produced $340,095.6

Just put the four values in cells and put them in the formula.