• 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.

Interest to be received for everyday but compound interval is "yearly"

Ram Mher

Member
Greetings !

interest to be calculated for everyday but compound interval is "yearly" therefore i have calculated in excel but result is not correct.
Compound formula Result in excel sheet is 22911 but result shall be 23088.
Required result -23088

Please find the attached excel sheet and favour me to correct this error.
 

Attachments

  • compound interest.xlsx
    10.1 KB · Views: 5
Hi ,

The formula for calculating compound interest using daily compounding , for a period of 395 days , would be :

=100000 * (1 + 0.21/365)^395

where 100000 is the initial amount , the annual rate of interest is 21%.

The Excel function which will give the same result as the above formula would be :

=FV(0.21/365, 395, , 100000)

I do not know how you want the result to be 123088.

Narayan
 
Hi ,

If we calculate the compound interest for 2 years , or probably even for any whole number of years , the results by the two methods match , giving 146410.

Since the compounded amount at the end of the first year is 121000 , it means the compound interest added in the second whole year is 146410 - 121000 = 25410.

If we now take the portion of this for the additional period in the second year of 30 days , we get the additional interest as :

25410 * 30 / 365 = 2088.49

Adding this to the 121000 , we get 123088.49

Narayan
 
@NARAYANK991
I agree. The formula as provided is correct for any integer number of years but returns interest per day that varies continuously whereas the scheme described has step changes at year boundaries. To get the piecewise linear amounts requires the amount to be interpolated for the remaining period lying within the final year.
 
@NARAYANK991
I agree. The formula as provided is correct for any integer number of years but returns interest per day that varies continuously whereas the scheme described has step changes at year boundaries. To get the piecewise linear amounts requires the amount to be interpolated for the remaining period lying within the final year.
Thanking a lot to everyone for the needful supports.

please suggest the formula to receive the result i.e 123088
Hi ,

If we calculate the compound interest for 2 years , or probably even for any whole number of years , the results by the two methods match , giving 146410.

Since the compounded amount at the end of the first year is 121000 , it means the compound interest added in the second whole year is 146410 - 121000 = 25410.

If we now take the portion of this for the additional period in the second year of 30 days , we get the additional interest as :

25410 * 30 / 365 = 2088.49

Adding this to the 121000 , we get 123088.49

Narayan

yes it is to be done.
 
Using names, the original formula reads
= initialAmount * (1+rate/Intv)^((days/365)*Intv)
[sorry about the changes but I am unable to read or see the significance of cell references in formulas; my maths degrees were pre-spreadsheet!]

To calculate for a whole number of years FLOOR(days,365)/365 one has
= initialAmount * (1+rate/Intv)^((FLOOR(days,365)/365)*Intv)
This is the start point for the remaining MOD(days,365) days of simple interest, giving a final formula of
= initialAmount * (1+rate/Intv)^((FLOOR(days,365)/365)*Intv)*(1 + MOD(days,365)*rate/365)
 
I
Using names, the original formula reads
= initialAmount * (1+rate/Intv)^((days/365)*Intv)
[sorry about the changes but I am unable to read or see the significance of cell references in formulas; my maths degrees were pre-spreadsheet!]

To calculate for a whole number of years FLOOR(days,365)/365 one has
= initialAmount * (1+rate/Intv)^((FLOOR(days,365)/365)*Intv)
This is the start point for the remaining MOD(days,365) days of simple interest, giving a final formula of
= initialAmount * (1+rate/Intv)^((FLOOR(days,365)/365)*Intv)*(1 + MOD(days,365)*rate/365)

i pasted it in excel but "#Name?"Error is coming
 
Using names, the original formula reads
= initialAmount * (1+rate/Intv)^((days/365)*Intv)
[sorry about the changes but I am unable to read or see the significance of cell references in formulas; my maths degrees were pre-spreadsheet!]

To calculate for a whole number of years FLOOR(days,365)/365 one has
= initialAmount * (1+rate/Intv)^((FLOOR(days,365)/365)*Intv)
This is the start point for the remaining MOD(days,365) days of simple interest, giving a final formula of
= initialAmount * (1+rate/Intv)^((FLOOR(days,365)/365)*Intv)*(1 + MOD(days,365)*rate/365)
Greetings !

Exact amount not receiving when i changing the compound interval from "yearly" to "monthly".

Detail is shown below:-
Total days-422
Rate-21%
Formula result-127182
Result needed-127215
 
Sorry, I didn't pay much attention to intervals other than one year. The approach of dividing by 12 to convert from annual interest rate to monthly interest rate is not one that comes naturally to me and, equally I do not know what conventions apply to the interpretation of a month as 30.416667 days given that month boundaries then do not align with day boundaries.

p.s. I tend the think of
monthlyRate = (1 + annualRate)^(1/12) -1
so I am not the right person to set up the formulas.
Good thing @NARAYANK991 is still coping :)!
 
Sorry, I didn't pay much attention to intervals other than one year. The approach of dividing by 12 to convert from annual interest rate to monthly interest rate is not one that comes naturally to me and, equally I do not know what conventions apply to the interpretation of a month as 30.416667 days given that month boundaries then do not align with day boundaries.

p.s. I tend the think of
monthlyRate = (1 + annualRate)^(1/12) -1
so I am not the right person to set up the formulas.
Good thing @NARAYANK991 is still coping :)!

Sir you are genius so pls supprt
 
Back
Top