1

I am having a question regarding the usage of the EFFECT() function in Microsoft Excel.

I know the basic concept of the function, and I have no issue with it when there are pre-defined compounding periods per year.

So, here is a picture of a normal table with the EFFECT() function: Click to see the picture

As you can see in the picture, when the periods are "fix", the usage of this function is obvious.

And here comes my problem. I would have to use this function precisely, in order to count the effective rate, BUT when the periods are not a fix number, but it is continous, so basically n --> infinite.

Now, this is a problem, because infinite is not a number. And you can't really tell Excel this, if my knowledge serves me right.

What would you guys do, in order to get the correct answer, when the period (n) is continuous, and NOT a fix number?

This is my precise problem that I would need to do with the EFFECT() function: https://i.stack.imgur.com/fF01a.png

As you can see, I did everything when 'n' was a fix number. But I also need to do it when it is continous. And I have no idea what to do in this case. Our professor only gave us this little symbol that n --> infinite. Which is cool, I understand, but don't know how to handle this with the EFFECT() function.

Thank you for your help!

Best regards!

Improve this question

2 Answers 2

Reset to default
1

The question is asking you to calculate the limit for the effective interest rate as period approach infinity (HERE). I think you need to look at the formula underlying the effect function as oulined in the link, there is also a method for determining the limit with example:

"The Limits to Compounding There is a ceiling to the compounding phenomenon. Even if compounding occurs an infinite amount of times—not just every second or microsecond but continuously—the limit of compounding is reached.

With 10%, the continuously compounded effective annual interest rate is 10.517%. The continuous rate is calculated by raising the number "e" (approximately equal to 2.71828) to the power of the interest rate and subtracting one. It this example, it would be 2.171828 ^ (0.1) - 1."

using the calculator on the PC it returns a limit of:

0.06183654654535962222468487716837

which excel translates to 6.18365465453596%

What subject is this for?

Improve this answer
1
  • Ohh my god, you are my savior! I used this formula that you just gave me (with the EXP() function) and it gave me the correct answer! This must be what the professor wanted us to do! Thank you so much! Hope he will accept it that it is not the EFFECT() function.. :D Yeah, this subject is roughly can be translated like "Basics of Banking" or something like that. I am studying IT economy, and we basically has to do economical calculations with computers as well. Our prof. wanted us to try to get the correct answer ourselves. Thank you, now I understand this! :)
    – Noxter
    Apr 29, 2020 at 12:32
2

First of all, welcome to superuser!

Edit: This is the answer to the question as it had been posted originally (technical question regarding the EFFECT formula). Please refer to Justin's answer to get the right solution for the adopted question.

As EFFECT() truncs Npery to an integer, you have to replace the formula and use (assuming the you are in row 1, col 1 contains the Nominal Rate and col 2 the Npery) in order to get suitable results for continous Npery.

=POWER(1+A2/B2,B2)-1

Regaring infinite: The maximum number in Excel is 9.99999E+307.

Attention: Excel computes wrong results for very high Npery (for 6%, greater then 10000000000000), as nominal_rate / npery becomes to small too calculate the right result. Moreover, as of Npery gets about 540168200000000, the result wrongly becomes 0 (zero), as

nominal_rate / npery

becomes 0 (zero), and 0 power Npery stays 0.

Improve this answer
8
  • Thank you for your help. I edited my original post with my exact "task" that I would have to do and all the information I have for it. Do you have any suggestion now, how should I try to do it with the EFFECT() function? I understand what you wrote, but I still have no idea how to do my problem. :(
    – Noxter
    Apr 28, 2020 at 17:26
  • What's the exact question your professor gave you? [Do you have to use the EFFECT function? continous | n -> infinite can mean many things, like it has to work for high values, it has to work for non-integer numbers, etc.] Might it be just enough to prove that your solultion eg. works for seconds per year (60*60*24*365)?
    – BogisW
    Apr 28, 2020 at 17:44
  • So we have a number of tasks with various excel functions that has to do with economy. We had to use the rate() function as well for example and many others. For every function, there is a little task, that we have to do with the specified function. In this case, he wrote, we MUST use the EFFECT() function, to calculate the effective rate based on the nominal rate (that is given) and based on 'n' (which is also given). Basically, we have to use EFFECT() to calculate all green coloured cells. I am not really sure what continous means in this case. (1/2)
    – Noxter
    Apr 28, 2020 at 18:02
  • The task says: We have to transform the nominal rates into effective rates, based on how frequently (in 1 year) the interest capitalization (English is not my first language so this may not be the correct term) occurs. The frequency of this interest "capitalization" is marked by 'n'. That is all we know.
    – Noxter
    Apr 28, 2020 at 18:04
  • OK, when I were you, I'd just provide more examples, up to Npery = 60*60*24*365, and comment, why it's not possible to use 'continuous' values, and add a row that says the result for infinite is infinite. You obviously cannot go beyond the limitations of excel when you are limited to the EFFECT function (support.microsoft.com/en-us/office/…) PS: When you feel that I answered your orginal question, an accepted answer would be nice. :-)
    – BogisW
    Apr 28, 2020 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.