How to enter in the formulae bar of the excel sheet the following problem :

Question

How to enter in the formulae bar of the excel sheet the following problem :

Vinod Gandhi 21

How to calculate interest at the progressive rate of interest with the following condition :

<=1 Lac. 4%

1 Lac <= 10 Lacs. 4.5%
10 Lacs <= 2 crore . 5%
2 crore <= 10 crore 4%
10 crores<=100 cr. 3.5%
100 crores. 3%

Kindly help me to solve this problem and I would appreciate if you could explain the logic behind the problem. Thank you.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Accepted answer

0 additional answers

Your answer

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer 1

Emily Hua-MSFT 27,796

@VinodGandhi-9660

----------------------
Update -----------------------

In order to calculate interest, we need to list all the mathematical formulas based on different principal amounts. According to your replies in comments below, it seems that you do not want to add auxiliary column to get the principal converted into Lakh, but want interest rate in Lakh unit in formula.

Here we use the letter X to represent the amount of principal, and it is not converted to the unit of Lakh, X/100000 represents the value in Lakh. (Put it in Excel, X represents the data in the cell.) The calculation formulas to get the interest in Lakh per year are as follows.

After some data calculations, the formula would be below.

If function allows we make logical comparisons to get the corresponding results.The first result is if your comparison is True, the second if your comparison is False. Its syntax is: IF(logical_test, value_if_true, [value_if_false])
.
But by one If function, we could only get: IF(X/100000<=1, X/100000*4%, [formula of X/100000>1]).
It means if X/100000 is smaller than or equal to 1, we apply the formula X/100000*4% to corresponding cell data, if not (X/100000 is bigger than 1), then apply the last formula for cell data.

As “Principal value range” is a continuous interval ranges, we need to nest multiple If functions on Excel to express the above mathematical formulas.
For the [formula of X/100000>1], we need to continue to split the “Principal value range”.
Such as IF(X/100000<=1, X/100000*4%,IF(X/100000<=10, 0.04+( X/100000-1)*4.5%,[formula of X/100000>10])), it means, if X/100000 is smaller than or equal to 1, we apply the formula X/100000*4% to corresponding cell data, if X/100000 is bigger than 1 but small than or equal to 10, use 0.04+( X/100000-1)*4.5% for cell data, otherwise use [formula of X/100000>10].

Then we continue to inline the If formula until it is subdivided to X/100000>10000.

Besides, as you want to get the interest per day, we need to divide the result by 365, you may try the following formula.
=IF(A2/100000<=1,A2/100000*4%,IF(A2/100000<=10,0.04+(A2/100000-1)*4.5%,IF(A2/100000<=200,0.445+(A2/100000-10)*5%,IF(A2/100000<=1000,9.945+(A2/100000-200)*4%,IF(A2/100000<=10000,41.945+(A2/100000-1000)*3.5%,356.945+(A2/100000-10000)*3%)))))/365*100000

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Vinod Gandhi 21 Reputation points

2021-07-08T07:27:15.197+00:00

The answer is not correct. I am using Office 2016 on my Mac. My data is as follows :
I have to calculate interest per day for the following amounts:
RS. 9137020
RS. 9158965
RS. 10692047

How to enter the formulae in the formulae bar?
Emily Hua-MSFT 27,796 Reputation points

2021-07-08T07:53:37.36+00:00

@VinodGandhi-9660
Thanks for your reponse, I think I misunderstood "the progressive rate of interest".
If there are multi judgment conditions, we can nested use the If function.

But does the data "RS. 9137020" mean 91.3702 Lakh?
I will use other data as a sample, and modify my previous reply.
Vinod Gandhi 21 Reputation points

2021-07-08T10:26:31.77+00:00

Hullo,

The below mentioned is the progressive rate of interest.
<=1 Lac. 4%
1Lac <= 10 Lacs. 4.5%
10 Lacs <= 2 crore . 5%
2 crore <= 10 crore 4%
10 crores<=100 cr. 3.5%
100 crores. 3%

I wish to find out the interest by the end of the day for the following amount:
RS. 9137020
RS. 9158965
RS. 10692047

The data "RS. 9137020" mean 91.3702 Lakh or you can say ninety one lakhs, thirty seven thousand and two.
Kindly send the modifications with explanations if possible.

Many thanks,
Vinod Gandhi
Vinod Gandhi 21 Reputation points

2021-07-08T12:35:35.767+00:00

Hullo again,

=IF(A2<=1,A2*4%,IF(A2<=10,A2*4.5%,IF(A2<=200,A2*5%,IF(A2<=1000,A2*4%,IF(A2<=10000,A2*3.5%,A2*3%)))))

the above mentioned formulae which you re-sent is again giving wrong results. I have to calculate the interest per day. So even if I divide the result by 365 (365 days in a year), still the answer is coming wrong. For example the interest per day should be RS. 1236.58 on RS. 9137020 . Kindly rework the solution and send.

Many thanks,

Vinod Gandhi
Emily Hua-MSFT 27,796 Reputation points

2021-07-09T09:07:47.283+00:00

@VinodGandhi-9660
Thanks for your example.
It seems that my previous idea of first reply is right. Let us check again.
The interest for a loan with principle 9137020 would be calculated as follows:
Interest per year = (100000 x 4%) + (900000 x 4.5%) + ((9137020-1000000) x 5%)) = 4000+40500+406851 = 451351
Interest per day = 451351/365 ≈ 1236. 58

I still convert source data into Lakh unit first, then let the result multiply by 100000 at the end, the formula for interest of per day should be =IF(A2<=1,A2*4%,IF(A2<=10,0.04+(A2-1)*4.5%,IF(A2<=200,0.445+(A2-10)*5%,IF(A2<=1000,9.945+(A2-200)*4%,IF(A2<=10000,41.945+(A2-1000)*3.5%,356.945+(A2-10000)*3%)))))/365*100000

If you want the formula without converting source data into Lakh unit, the formula for interest of per day should be =IF(A2<=100000,A2*4%,IF(A2<=1000000,4000+(A2-100000)*4.5%,IF(A2<=20000000,44500+(A2-1000000)*5%,IF(A2<=100000000,994500+(A2-20000000)*4%,IF(A2<=1000000000,4194500+(A2-100000000)*3.5%,35694500+(A2-1000000000)*3%)))))/365
Vinod Gandhi 21 Reputation points

2021-07-12T07:41:46.427+00:00

Thanks for your answer.

I tried the formula without converting source data into Lakh unit, which is : the formula for interest of per day =IF(A2<=100000,A2*4%,IF(A2<=1000000,4000+(A2-100000)*4.5%,IF(A2<=20000000,44500+(A2-1000000)*5%,IF(A2<=100000000,994500+(A2-20000000)*4%,IF(A2<=1000000000,4194500+(A2-100000000)*3.5%,35694500+(A2-1000000000)*3%)))))/365. The answer comes correct.

But if I convert source data into Lakh unit first, then let the result multiply by 100000 at the end, the formula for interest per day. =IF(A2<=1,A2*4%,IF(A2<=10,0.04+(A2-1)*4.5%,IF(A2<=200,0.445+(A2-10)*5%,IF(A2<=1000,9.945+(A2-200)*4%,IF(A2<=10000,41.945+(A2-1000)*3.5%,356.945+(A2-10000)*3%)))))/365*100000.

This formulae returned wrong answer. I tried to use my example of the interest for a loan with principle 9137020. The answer comes to 75114395.9

Many thanks for the first formulae. But could you kindly try to find fault with the second formulae when you convert source data into Lakh unit.

thanks,

Vinod Gandhi
Emily Hua-MSFT 27,796 Reputation points

2021-07-12T09:52:22.04+00:00

@Vinod Gandhi
Could you please take a screenshot of your data, the wrong results with the formula =IF(A2<=1,A2*4%,IF(A2<=10,0.04+(A2-1)*4.5%,IF(A2<=200,0.445+(A2-10)*5%,IF(A2<=1000,9.945+(A2-200)*4%,IF(A2<=10000,41.945+(A2-1000)*3.5%,356.945+(A2-10000)*3%)))))/365*100000?

As on my sample these 2 formulas get the same results, so I need to confirm how do you convert source data into Lakh unit first.
I suggest you use the formula =A2/100000 to get the data in Lakh unit like following image.
Vinod Gandhi 21 Reputation points

2021-07-13T03:09:26.323+00:00

Hullo,

As suggested I am attaching the screenshot of the formulae which returned wrong answer. I tried to use my example of the interest for a loan with principle 9137020. The answer comes to 75114395.9.
Emily Hua-MSFT 27,796 Reputation points

2021-07-13T09:06:28.98+00:00

@VinodGandhi-9660
I would modify my previous "answer" for more detailed information.
Vinod Gandhi 21 Reputation points

2021-07-13T14:02:35.773+00:00

Thanks...
I am awaiting your modifications of the answer for more detailed information.
Emily Hua-MSFT 27,796 Reputation points

2021-07-15T07:48:50.687+00:00

@VinodGandhi-9660
I have updated my previous "answer", please have a check.
Vinod Gandhi 21 Reputation points

2021-07-16T14:48:13.133+00:00

Many thanks Emily for your answers.

I appreciate your patience.

Vinod Gandhi

Share via

How to enter in the formulae bar of the excel sheet the following problem :

0 additional answers

Your answer