seriessum & array combination to solve a problem?

[resolate]

Hello all!

I am having a serious problem in excel and i keep getting stuck. I ll try to explain the problem as good as possible.

My wife and I are looking to buy a house. As i am somewhat of an analytic person, i want to calculate costs & gains over a long period of time. In order to do this i am setting up multiple scenarios. The problem starts with the calculation of the long term saving that we would lose if we decide to buy a house right now.

The saving part:

In the saving part i have different operators and i want them to be changeable. This is why i want all my formulas to relate to these operators. Examples are: period: 10 yrs; interest rate, loyalty bonus, etc...
So for the simple calculation of the ammount we would save over for example 10 years i have designed the following formula:

(=(A5*(1+G5)^L4)+((C5*(1-(1+G5)^L4))/(1-(1+G5))))

Where:
A5 = start ammount on account
G5 = interest rate supplied by financial organisation
L4 = ammount of capitalisations (or period)
C5 = recurring ammount to be saved every month

This calculation is pretty easy and everything can be changed easily in the operators to change a scenario.

The loyalty bonus:

The problem in my calculation is the loyalty bonus. This is the interest rate the financial organisation would give us every year at the end of the year, on the ammount saved that has not been withdrawed from the bank.

I was thinking of doing this with the seriessum formula. Since it works as shown below.

ZA006051246.gif

What i need is that every period is calculated and added (with the formula from above). So if we would go in years i would want something like this:

[ (calculation of ammount saved over 1 year) * loyalty bonus ] + [ (calculation of ammount saved over 2 years) * loyalty bonus rate ] ... etc

I can make this formula, only it involves me manually typing the following array in the coefficients part of the seriessum formula: {1,1,1,1,1,1,1,1,1,1} which stands for 10 years.
This means that if i change my original operator of years (which is now 10) to lets say 20, the formula wont be good anymore... What i actually want from excel, is to automaticly make me an array with "1s" of the ammount that is filled in in the period operator box.

So when it is 5 years excel should generate: {1,1,1,1,1} ; when it is 2 years excel should generate: {1,1} because i need these arrays to work in my formula. I know that array constants can only use numbers or text, and not formulas, so this is the part where i am really stuck...

Can anyone help me?

I thank you in advance.

Regards,

Resolate

[XOR LX]

Hi.

The following construction:

ROW(INDIRECT("1:"&A1))^0

will generate an n-row-by-1-column array of n elements consisting entirely of 1s, where n is equal to the value in cell A1.

Note that this construction will not in itself generate that array, since there is no external function acting upon it to coerce an array of returns. Any suitable function will suffice, though whether the resulting construction also requires entering as an array formula or not (i.e. with CTRL+SHIFT+ENTER) depends upon what that external function is.

For example, with A1 containing 5, a construction such as:

=SUM(ROW(INDIRECT("1:"&A1))^0)

array-entered, will resolve to:

=SUM({1;1;1;1;1})

If your particular circumstances require that this array of 1s be a single-row array (as opposed to the single-column array here - note that, in Excel, semi-colons and commas represent row- and column-separators respectively), then you will need to transpose it, viz:

=SUM(TRANSPOSE(ROW(INDIRECT("1:"&A1)))^0)

which will now resolve to:

=SUM({1,1,1,1,1})

It's a little difficult to help you any further at this stage since you haven't provided a workbook.

Regards

[resolate]

XOR LX,

Thank you for your swift reply...

The formula you provided me is working, only i have encountered another problem in my solution.

I will try to give an example of what i want to accomplish:

Lets say we have following data:

Period: 10 years
interest rate: 0,003 / year = 0,002496569 / monthly capitalisation
Loyalty bonus: 0,001
Recurring ammount to be saved each month: 100€

In this example i want to calculate what i will have saved in 10 yrs time, if i save 100€ each month with an interest rate of 0,002496569 of monthly capitalisation and a yearly loyalty bonus of 0,0001.

So pure math now:

For the calculation of the saved ammount without the loyalty bonus, we would have a mensuality with the following formula:

[ recurring save * (1-(1+ i)^n) ] / (1-(1+i) ]

In the above example: [ 100 * (1 - (1 + 0,002496569)^120) ] / (1 - (1 + 0,002496569)
This will give us the ammount saved over 10 yrs included monthly interest. Except here we didnt include the loyalty bonus (which is only capitalised once a year).

So the formula i would need to do this is something like this:

[ (ammount saved over 1 year) * (loyalty bonus rate) ] + [ (ammount saved over 2 years) * (loyalty bonus rate) ] + ... + [ (ammount saved over 10 years) * (loyalty bonus) ]

As you can see it is already starting to look a little bit like the seriessum formula which i intended to use... Although i have obviously mistaken myself.
For it to be completely automatic, i want excel to automaticly calculate and make a sum of the above listed formula. And i want it to only use the data as i gave above.

I come to the conclusion that the seriessum might not be the right formula for this. Do you have any suggestions? Or do i need to be more clear in some other way?

[MrShorty]

I am not familiar with these financial calculations. From what little I know, I think what you want will be related to the PV or FV or related functions. I would refer you to the help file for further insight into these functions: https://support.office.com/en-us/art...8-4fe4bb1b71b3
https://support.office.com/en-us/art...__toc309306711

[resolate]

Shorty,

Thx for your reply...

I do know the financial formulas in excel. The formula i could use is the FV() function. Which calculates the ammount saved over "n" years with a fixed ammount deposited each month for example. The problem is that i want to add a loyalty bonus to this formula... As the FV() function uses the intrest rate (which is calculated at the end of every month) i need something more to insert the loyalty bonus, which is another rate and is only calculated once a year...

In a nutshell:

If i want to know what i saved in 2 years for example, i can use the FV() function to calculate this ammount but it is excluding the loyalty bonus.
So in order to get the result i want i d have to do it like this:

fv(1year)*loyalty bonus rate + fv(2years)*loyalty bonus rate.

This would give me the exact ammount i want to get... The problem is i do not want to do this manually, i want to design a formula that does this for me and over a period that can be specified.

For 2 years i could do it manually, but when i change the period to 10 years, i need to start all over again and i need to type it all myself, which is ofcourse not the idea... Moreover i want to calculate it with a formula that can return this value in 1 cell...

Is it more clear?

Should i try to add an excelsheet?

Thx in advance

[MrShorty]

I find that, if I look at a problem mathematically/algebraically before I look at it programmatically, that I often do better programming the solution.

To make sure I understand -- is this the correct formula: total=sum for i=1 to n of fv(i)*loyalty? If this is correct (loyalty bonus is a constant), then I would start with the distributive property (I know, this is going clear back to the earliest lessons of algebra) http://www.mathwarehouse.com/diction...d-examples.php

AB+AC+AD+AE...=A*(B+C+D+E+...)
In your case, sum(fv(i)*loyalty)=loyalty*sum(fv(i)). I'm not real familiar with what is going internally in the fv() function, though it seems like it is already a summation of some kind, so this might simplify even further. Assuming the loyalty bonus is a constant, something like this should allow you to use the existing functions to get what you want.

[resolate]

Shorty,

Yes this is the same mathimetical formula as i wrote above. And you are right, the loyalty bonus is a constant factor.

I have added an excelsheet with an example. I am working with a dutch office, so you should now that the formula TW() equals FV() in english.

As you can see i have calculated the loyalty bonus over 2 years and over 3 years in 2 scenarios. The problem is that i want excel to generate this (n*1) row of TW() calculations for me, dependant on the period of the investment. You can see that over 2 years, i need to calculate a TW() value 2 times (once over 1 year, again over 2 years multiplied by the loyalty bonus). Over 3 years i need to calculate the TW() formula 3 times, and over 100 years i would need to type it 100 times... This is the part that i want to be automated. Note that in this calculation of the row it should generate, there is a change in each term:

=(G6)*((-TW(((G3+1)^(1/12)-1);G4*12/G4;G2))+(-TW(((G3+1)^(1/12)-1);G4*12/G4*2;G2))+(-TW(((G3+1)^(1/12)-1);G4*12/G4*3;G2)))

in more detail: it is the period part in each section of the TW() formula that needs to be changed, because if it is over 10 years, i need a row which calculates TW(1 year) + TW(2year) + TW(3year)

This is why you can see i added the following formulas in this period part:

For 1 year: G4*12/G4
for 2 years: G4*12/G4*2
for 3 years: G4*12/G4*3

So i want excel to create some sort of array on a (n*1) row of soms dependant on the number of periods i have in my excel sheet and moreover, none of these terms in the row would be exactly the same...

Do i make my problem more clear this way?

I thank you all in advance

Grtz,

Resolate

[MrShorty]

Did you understand what XOR LX explained about using the ROW() function to get an array of integers? One way to approach this is just like he suggested, use the ROW() function to generate an array of integers.

In the sample file, I have added a block of cells that illustrates how this works. Under "i", there is an array function that illustrates how to use the ROW() function to generate an array of integers. fv(i) shows an array function where I have included the same ROW() array function syntax in the nper argument of the FV function to generate an array of fv(i)'s. From there, the loyalty bonus and total are readily calculated.