3 Replies Latest reply on Aug 26, 2013 3:20 AM by steve_ssh

    how to calculate annual rate of return on investment

    danielgardner

      I really have looked for the answer to this question and went through the built in claculations and couldn't find anything that would work. I've created a database that tracks homes purchased, fixed up and sold and would like to track the annual rate of return which appears to require a special calculation to complete. Since I track the expenses, dates, sale price - I believe I have all the data I need - I just can't figure out how to get the actual result. It looks like it requires multiplying by the root = representing the number of years held.

       

      It appears more complicated than I expected. Any help would be appreciated!

       

      Dan

        • 1. Re: how to calculate annual rate of return on investment
          SteveMartino

          Are you asking how its calculated?  Or how to use the functions to calculate?

          Did you try the financial functions? Or some custom functions:

          http://www.briandunning.com/filemaker-custom-functions/results.php

          • 2. Re: how to calculate annual rate of return on investment
            danielgardner

            Here is the simplest calcualtion I've found for calculating an annual compound rate;

             

            Calculating Compound Annual Growth Rate (CAGR)

            In order to calculate CAGR, you must begin with the total return. In our above example, the total return was 2.3377 (133.77%). We also know the investment was held for ten years.

            Multiply the total return (2.3377) by the X root (X being the number of years the investment was held). This can be simplified by taking the inverse of the root and using it as an exponent. In our example, 1/10, or .10 (had the number of years been 2, you could have taken 1/2 or .5 as the exponent, 3 years would be 1/3 or .33 as the exponent, four years would be 1/4 , or .25, and so on and so forth.)

            In our above example, CAGR would be calculated as follows:

            2.377(.10) = 1.09, or 9% compound annual growth rate (again, recall the 1.0 represents the principal value which must be subtracted; ergo, 1.09 - 1.0 = .09, or 9% CAGR expressed as a percentage).

            In other words, if the gains on the Pepsico investment were smoothed out, the investment grew at 9% compounded annually. To check the result, use the future value of a single amount. In essence, this means that if the investor had taken the $15,000 to a bank for ten years and earned 9% on her money, she would have ended up with the same balance of $35,300 at the end of the period.

             

            I was going to build a calculation, to copy these steps, but I got thrown off by the part I bolded and underlined. How do you perform that root equation? Can it be dynamic for multiple entries based on different total returns and length of time investment was held? I was hoping to crete a calculation using the information from the database fields to genrate a report showing the annual growth rate for each entry and averaged toal.

            The more I talk about it the more complicated I find it to be. I've been unable to find a simple calculation for this.

             

            Thanks!

            Dan

            • 3. Re: how to calculate annual rate of return on investment
              steve_ssh

              danielgardner wrote:

               

              Here is the simplest calcualtion I've found for calculating an annual compound rate;

               

              ...

               

              In our above example, CAGR would be calculated as follows:

              2.377(.10) = 1.09, or 9% compound annual growth rate (again, recall the 1.0 represents the principal value which must be subtracted; ergo, 1.09 - 1.0 = .09, or 9% CAGR expressed as a percentage).

              ...

              I was going to build a calculation, to copy these steps, but I got thrown off by the part I bolded and underlined. How do you perform that root equation?

               

              Hello Daniel,

               

              Within the FileMaker calculation expression edtior dialog, the above equation (which you put in bold) would read as follows:

               

                  2.377 ^ ( 1/ 10 )

               

               

              The exponential operator ( ^ ) is available at the bottom of the scrolling operator list (just after the logical function operators).

               

              data viewer nth root.png

               

              HTH & Best,

               

              -steve