How long will my capital last

[Durhamr]

Hi

If I had a capital sum of say £100,000 and I was taking an annua
income of 5% and I was getting 3.5% interest annually - Can you help m
demonstrate how long my capital last?
Thank you
Dust

 

[Anders S]

Dusty,

=NPER(3.5%,-5000,100000,0)

If, by 5%, you mean 5% of the original investment (£5.000 each year), your money will last for approximately 35 years at 3.5% interest.

See Excel Help for more info on NPER().

Regards,
Anders Silven

 

[Durhamr]

Anders
Thanks very much - I did mean 5% of the original capital - however
also meant to include within the question that the income I tak
annually increases each year by say 2% cna that also be included it th
calculation?
Regards
Dusty

p.s. first time user amazed at the speedy response - once again thank

 

[Anders S]

Dusty,

Can't figure out a formula right now, but a quick and dirty way is to use worksheet functions.

Enter the following values/functions in cells

A1: 100000
B1: =A1*0.035
C1: =-5000
D1: =SUM(A1:C1)

A2: =D1
B2: =A2*0.035
C2: =C1-(2%*5000)
D2: =SUM(A2:C2)

Then select A22, fill down, and you'll see that you will run out of money after about 25 years.

You can replace the fixed parameters in the formulas (e.g. 0.035, -5000 and 2%*5000) with references to cells where you can enter different input values to the calculation.

Regards,
Anders Silven

 

[Durhamr]

Anders

Thanks very much - Iv'e not tried your suggestion as yet (been away
however I will work on it - I hope you don't mind if I come back to yo
as I am still very new to Excel.

Best regards
Dust

 

[Durhamr]

Anders

Thanks I tried out your last suggestion and the calculation is fine
-the only bit I couldn't work out was how to use the 'Fill' function -
as I am relatively new to excel - I tried the help file to no avail -
therefore if you are feeling benevolent any tip on how to will be much
appreciated.

Best regards
Dusty

 

[Anders S]

Dusty,

To fill down:

- select A22
- point at the little black square in the bottom-right corner of D2 and drag it down as required. The formulas will adjust automatically.

HTH
Anders

 

[Durhamr]

Anders

Thanks very much - by the way happy christmas
Regards
Dusty

 

[Norman Harker]

Hi Dusty!

You can use Goalseek on the following function:

Function PV1LeaseEqYield(Term As Double, Review_Freq As Double,
Equated_Rate As Double, Growth_Rate As Double) As Double
Dim Top
Dim Bottom
Top = 1 - ((1 + Growth_Rate) ^ Term / (1 + Equated_Rate) ^ Term)
Bottom = 1 - ((1 + Growth_Rate) ^ Review_Freq / (1 + Equated_Rate)
^ Review_Freq)
PV1LeaseEqYield = PV(Equated_Rate, Review_Freq, 1, 0, 0) * (Top /
Bottom)
End Function

Using:

=PVnLeaseEqYield(20,1,3.5%,2%,5000)
Returns: -84404.5109070671

With cell references for my data:

=PVnLeaseEqYield(B1,B3,B2,B4,B5)
Returns: -84404.5109070671

I then use Goalseek to find how this formula will return -100000 by
changing B1 (the term) and I get 24.4317997425098

I can't say that I'm happy with the solution but it acts as a check of
the long hand version.

There is almost certainly an explicit version of the formula used in
the function for Term but it's a bit early in the morning here to be
playing around with logs.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Harlan Grove]

If I had a capital sum of say £100,000 and I was taking an annual
income of 5% and I was getting 3.5% interest annually - Can you help me
demonstrate how long my capital last?

For the heck of it, someone ought to give the single formula solution. For the
problem as stated exactly above, the number of years capital lasts is given by

=NPER(r,1,-1/p) where r = 0.035 and p = 0.05.

This may be derived by observing that capital K[t] at any point in time t is the
accumulated value of original capital less the present value at time t of the
outflows from original capital.

K[t] = K[0] * (1+r)^t - p * K[0] * Sum((1+r)^n, n = 0..(t-1))

Since you want to find the smallest t at which K[t] <= 0,

0 = K[0] * (1+r)^t - p * K[0] * Sum((1+r)^n, n = 0..(t-1))

0 = (1+r)^t - p * Sum((1+r)^n, n = 0..(t-1))

0 = 1 - p * Sum((1+r)^-n, n = 1..t)

1/p = Sum((1+r)^-n, n = 1..t)

from which the NPER function call follows. Note: this assumes outflows from
capital occur at the end of each year in one lump sum.

If the outflows from the original capital increase by 2% (g) per year, but the
first year is just 5% (p) of original capital, then the NPER function becomes

=NPER((1+r)/(1+g)-1,1/(1+g),-1/p) where r = 0.035, g = 0.02 and p = 0.05.

The algebra for this one is left as an exercise.

 

[Sandy Mann]

For the heck of it, someone ought to give the single formula solution. For the
problem as stated exactly above, the number of years capital lasts is given by

=NPER(r,1,-1/p) where r = 0.035 and p = 0.05.

This may be derived by observing that capital K[t] at any point in time t is the
accumulated value of original capital less the present value at time t of the
outflows from original capital.

K[t] = K[0] * (1+r)^t - p * K[0] * Sum((1+r)^n, n = 0..(t-1))

Since you want to find the smallest t at which K[t] <= 0,

0 = K[0] * (1+r)^t - p * K[0] * Sum((1+r)^n, n = 0..(t-1))

0 = (1+r)^t - p * Sum((1+r)^n, n = 0..(t-1))

0 = 1 - p * Sum((1+r)^-n, n = 1..t)

1/p = Sum((1+r)^-n, n = 1..t)

from which the NPER function call follows. Note: this assumes outflows from
capital occur at the end of each year in one lump sum.

If the outflows from the original capital increase by 2% (g) per year, but the
first year is just 5% (p) of original capital, then the NPER function becomes

=NPER((1+r)/(1+g)-1,1/(1+g),-1/p) where r = 0.035, g = 0.02 and p = 0.05.

Yer, that's just what I was thinking. <g>

 

[Norman Harker]

Hi Harlan!

Nice one!

Checks out as well with my iteration of a formula used for a different
purpose where we have non-annual reviews of the payments.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

If I had a capital sum of say £100,000 and I was taking an annual
income of 5% and I was getting 3.5% interest annually - Can you help me
demonstrate how long my capital last?

For the heck of it, someone ought to give the single formula solution. For the
problem as stated exactly above, the number of years capital lasts is given by

=NPER(r,1,-1/p) where r = 0.035 and p = 0.05.

This may be derived by observing that capital K[t] at any point in time t is the
accumulated value of original capital less the present value at time t of the
outflows from original capital.

K[t] = K[0] * (1+r)^t - p * K[0] * Sum((1+r)^n, n = 0..(t-1))

Since you want to find the smallest t at which K[t] <= 0,

0 = K[0] * (1+r)^t - p * K[0] * Sum((1+r)^n, n = 0..(t-1))

0 = (1+r)^t - p * Sum((1+r)^n, n = 0..(t-1))

0 = 1 - p * Sum((1+r)^-n, n = 1..t)

1/p = Sum((1+r)^-n, n = 1..t)

from which the NPER function call follows. Note: this assumes outflows from
capital occur at the end of each year in one lump sum.

If the outflows from the original capital increase by 2% (g) per year, but the
first year is just 5% (p) of original capital, then the NPER function becomes

=NPER((1+r)/(1+g)-1,1/(1+g),-1/p) where r = 0.035, g = 0.02 and p = 0.05.

The algebra for this one is left as an exercise.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup

archives.

 

[Anders S]

You're welcome, Dusty, and a Merry Christmas to you too.

Anders

 

[Durhamr]

Norman

I very much appreciate your input - It’s going to be interesting
deciphering your formulae this Christmas or do I mean next, next,
next…. Thanks very much
Regards
Dusty