• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compound Interest Quarterly

R

rajkenya1

Member
Hi Guys


Need some assistance on a compounding formulae again.


Attached in the spreadsheet I have shown an opening balance, and amounts coming in and out quarterly. I want a formula in cell c10 which will give me the total interest over one year. Note the interest is earned quarterly though.


Please help
 

Attachments

Hi Nebu

Thanks for your reply. However your formula does not take consideration of the money coming in and out assuming the trend continues for the year. I need a formula that captures that as well. Thanks though buddy
 
Hi Narayan

I think you may be headed in the right direction but somehow the interest calculation formula seems to be incorrect. Simply done on a calculator the interest earned for the first quarter should be 11254049+1347534-911936=11689647*3.52/4.06=$101348.

What i was hoping for is a one formula that would capture this all rather than having multiple lines.

Is it possible?
 
Hi Raj ,

Sorry , I am not into these single-cell formulae. Laying it out properly is the first step towards understanding whether the logic is correct. If you can confirm from the steps where the calculation is wrong , it will help.

Narayan
 
Hi Narayan

Thanks for your reply. Ok yes the logic is definitely correct but am not sure if the quarterly interest calculation is correct. Perhaps do a check manually and then see if the formula captures it.

Thanks for your help sir
 
Hi Raj ,

I assume that the money paid in and out is only after the quarters are over ; if they are at the beginning of the quarters , then it means when we start for the first quarter , the money which earns interest for the first quarter is the Principal + Money Paid In - Money Paid Out ; is this correct ?

Narayan
 
Let me try to understand , how can you pay quarterly interest before hand if the subsequent flow of money is on a quarterly basis, As per your formula you are paying the interest assuming that all the money is in hand at the starting itself, please correct me if I am wrong....
 
Hi

I was interested in this because it took me back to school days. This is the old compound interest stuff. I did what Narayan did and worked it out long hand

=C4*(1+(C7)/4)^$C$8+(C5-C6)*(1+(C7)/$C$8)^3+(C5-C6)*(1+(C7)/$C$8)^2+(C5-C6)*(1+(C7)/$C$8)^1+(C5-C6)

Then I used my long hand to construct some Excel formula to do the same thing.

=C4*(1+(C7)/C8)^C8-FV(C7/C8,C8,C5-C6)

Then I stepped back and said now the answer depends on whether the compounding interest for the payments is paid at the start or the end of the quarter. I suspect this is where the difference between Narayan and my end result lies. I have to look into it after I get back from lunch.

Should put you on the right track. I used to do all this with a calculator. File attached to show workings.

Smallman
 

Attachments

Hi Narayan, Nebu and SmallMan

Thanking you for taking interest in this and helping me out.

We have to make the assumption that the money in and out is at the end of the quarter. This same trend continues for the year (quarterly though).

Smallman i think you are on the right path as well with a single formula. However apart from the end balance i want to know the total interest earned.

Thanks again all of you for your help
 
@rajkenya1

I made the assumption that the interest was paid at the end of the quarter. Calculating the interest earned then is a simple matter of arithmetic. Just take

principal + 4 * (In - Out)

and you have your answer.

Take care

Smallman
 
Is it something like this =((SUM(C4:C5)-C6)*(1+(C7/4.06))^(4*C8))-(SUM(C4:C5)-C6. I am assuming a constant cash inflow every quarter...
 
You must log in or register to reply here.
Back
Top