We help IT Professionals succeed at work.

excel to calculate interest...

Medium Priority
109 Views
Last Modified: 2020-01-19
Hi,
I want to compare between different savings offers.

Here are the details :
All offers have 2 different "punishments"
1) X percent per any monthly deposit (calculated immediately upon deposit).
2) Y percent per accumulated money. This calculation is somehow complicated :
    2.1) The collection is made once in 3 months
    2.2) The interest rate is calculated in a manner so that the "yearly compound interest rate" would be "Y". For example if the yearly interest rate is 2%, than the quarterly interest rate would be something like 0.49%.

The excel sheet should accept two numbers (initial accumulation + monthly deposit).
The output should give me the results at the end of the year :
A) How much is accumulated in each method
B) How much did I pay in each method.



Thanks
zmau
Comment
Watch Question

Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
I mocked up a spreadsheet that performs the calculations as I understood your description. Please correct any misunderstandings on my part.

The input values are the four cells with Blue font color (Q1:Q4)
Investment-AlternativesRev1.xlsx
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
I'm a little unclear about the "punishments." Are you talking about fees that are collected at X or Y percent? If so, what are the interest rates earned on the savings account and how might they be compounded? That will have a great bearing on the calculations.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I decided that you might be trying to model returns on Class A versus Class B shares in a mutual fund. So I added columns for share prices and number of shares, plus inputs for expected annual return and standard deviation of that return.

Depending on the fees charged and the expected earnings, you may find it advantageous to convert from one share type to the other after a certain number of years.
Investment-Alternatives.xlsx

Author

Commented:
what are the interest rates earned on the savings account
- I am not from an English speaking country - so I might have mislead you (with the word "saving"). I any case, there is no interest in these "programs". This "program" is used for me to save for my future (retirement time). That is also why I named it "punishments".

you might be trying to model returns on Class A versus Class B shares in a mutual fund
- I am unfamiliar with the terms (I will ask an American friend to be sure :-)).

Basically this is a program to save money for my future (retirement time). All programs basically invest the money, and the money is accumulated for my retirement time. And I just want to understand what are the different "punishments". Of course I have no way to estimate the return on the investments (but that is a different story....

Maybe a better word would be "management fees" (instead of "punishments").

Thanks a lot.

In any case I will look into the excel files in a minute.

Author

Commented:
Thanks all.
The first excel from "byundt", does the job.
I have a question for "byundt" :
Can you please explain the bolded part of the following formula "=(MOD(ROWS(D$2:D5),3)=1)*SUM($Q$3,J$1:J4)*((1+$Q$2)^0.25 - 1)" ??
Why not simply write "J4" instead of "J$1:J4" ?
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
The total money invested (for management fee based on account balance) is the sum of the initial plus the monthly investments. Because SUM function ignores text, it is OK to start the sum with the header label row.

Author

Commented:
Thanks,
The first sentence is exactly correct.
I was not troubled  by adding "J1" (the header) into the SUM.
The way I understood "SUM($Q$3,J$1:J4)" is  "J2" + "J3" + "J4" + "Q3" (which is the initial values). Am I correct ?
My understanding of the correct calculation is "SUM($Q$3,$J$4)". which means "J4" + "Q3".
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Oops!

Your understanding on how SUM works is exactly correct. My attempt to calculate expenses on assets under management--not so much.

The reference to column J in that formula ought to have been to column I. The formula in cell K4 should be:
=(MOD(ROWS(D$2:D5),3)=1)*SUM($Q$3,I$1:I4)*((1+$Q$2)^0.25 - 1)

Open in new window


I updated the attached file to my earlier Comment to reflect that fix.

Brad

Author

Commented:
Thanks a lot...