Math question

[zenitro]

How do I calculate the interest that I need to achieve.

If
PMT = 5000/yr
N = 20yr paying the 5000. and another 20 yr just holding it without additional capital.
FV = lets say $500,000.
PV = 0

Does anyone know how to calculate this? Would prefer a formula answer. Everything forget give back teacher liao
Online financial calculator only allow for event where N= amount of years to put in capital. Dont have half/half option. Might just be that I am googling wrongly.

Thanks in advance

 

[tangent314]

This one probably requires the use of XIRR to calculate, best done in a spreadsheet.

 

[dork32]

tangent is only partially correct. because you need a spread sheet and more than that

the thing is there is no analytical solution only numerical solutions through iterations. that is you have to guess the interest rate till you get your answer. this is similar to the guess and check method used in primary school.

but primary school teachers are not the best maths experts. the method they developed is tedious.

so the correct method is this:
guess a rate eg 2%
use the fv formula to get the future value for the first 20 years
use the fv formular to get the future value for the next 20 years
use the goal seek or solver function to adjust such that you can the 500k you want.
the goal seek allows excel to do the guessing for you, instead of you gong gong guess until tomorrow like primary school kids and teachers.
you can go watch youtube on how to use the goal seek function

i did it and get a return of 5.44185%

 

[dork32]

How do I calculate the interest that I need to achieve.

If
PMT = 5000/yr
N = 20yr paying the 5000. and another 20 yr just holding it without additional capital.
FV = lets say $500,000.
PV = 0

Does anyone know how to calculate this? Would prefer a formula answer. Everything forget give back teacher liao
Online financial calculator only allow for event where N= amount of years to put in capital. Dont have half/half option. Might just be that I am googling wrongly.

Thanks in advance

you have your financial calculator, you use terms like pmt and pv. it means you got some standard.

you can use the return that i gave you to check if i am correct.

 

[zenitro]

tangent is only partially correct. because you need a spread sheet and more than that

i did it and get a return of 5.44185%

Ya. the data is just random number. I was wondering if anybody have something like an online calculator or excel spreadsheet that they can share so I can do the calculation while changing the values.

 

[tangent314]

XIRR already does iterating internally (in fact, you can specify the first guess), so there is no need to manually iterate yourself.

https://docs.google.com/spreadsheets/d/1ZgHyHUr4ff-hDbe-6cONahLXyrGjCsS_1jG93pRy4AA/edit?usp=sharing

Note: If you are getting a result of 5.44% instead of 5.26%, that is because you have set the TVM calculator into END mode instead of BEGIN mode. If you are using FV/PV series of equations, they assume END mode. For this case, since payment is done at the start of the periods, BEGIN should be used.

 

[dork32]

Ya. the data is just random number. I was wondering if anybody have something like an online calculator or excel spreadsheet that they can share so I can do the calculation while changing the values.

you can just set up your spreadsheet to allow end users to key in different values of pmt and pv and other things. you can then use the goal seek for the answer.

 

[dork32]

XIRR already does iterating internally (in fact, you can specify the first guess), so there is no need to manually iterate yourself.

https://docs.google.com/spreadsheets/d/1ZgHyHUr4ff-hDbe-6cONahLXyrGjCsS_1jG93pRy4AA/edit?usp=sharing

Note: If you are getting a result of 5.44% instead of 5.26%, that is because you have set the TVM calculator into END mode instead of BEGIN mode. If you are using FV/PV series of equations, they assume END mode. For this case, since payment is done at the start of the periods, BEGIN should be used.

your spread sheet requires 21 lines and 2 columns to get the answer

my method requires 3 cells
1 cell for the interest
1 cell for the first 20 years
1 cell for the last 20 years.

 

[dork32]

Note: If you are getting a result of 5.44% instead of 5.26%, that is because you have set the TVM calculator into END mode instead of BEGIN mode. If you are using FV/PV series of equations, they assume END mode. For this case, since payment is done at the start of the periods, BEGIN should be used.

you are right. i did not do this adjustment
my first 5000 comes in on the 1st day of the second month
your first 5000 comes in on the 1st day of the first month.

this is the problem with pv and fv and all the financial functions. sometimes it takes a bit of time to visualize.

 

[dork32]

my 3 cells are
5% (C1 Cell)
=FV(C1,20,5000,0,1) (C2 Cell)
=FV(C1,20,0,C2,1)

followed by the goal seek for 500k

xirr formula is good for irregular flow of money. for simple situations, FV/PV functions would suffice.