none
P*((1 + i)^n -- possible to show separate steps on report? RRS feed

  • Question

  • I have a report with 20 rows of unbound text boxes, 4 boxes to a row. All are set Visible = False.

    In the report’s On Activate event, a computation is made using CurrentValue = P*((1 + i)^n.

     

    For eample:

    P = $100.00  (starting amount)

    i = .07  (for 7% interest compounded annually

    n = 3   (for 3 years)    Answer: $122.50

     

    This gives me the correct CurrentValue, but I would like to show how the values after each year were computed. In this example, there would be 3 rows (one for each year), Visible = True for each box in each row.

    The report showing:

       (n)          (P)            (i)      CurrentValue

        1      $100.00   x   1.07   =   $107.00

        2      $107.00   x   1.07   =   $114.49

        3      $114.49   x   1.07   =   $122.50

     

    Is it possible to do this – to separate CurrentValue = P*((1 + i)^n into “n” steps where the appropriate values could be assigned to the appropriate boxes on the report after each step?

     

    What about the following?

     

    Sub Single_Loop_Example

    Dim n As Integer

    Dim P As Currency

    Dim i As Double

    Dim CurrentValue As Currency

     

    If n > 0 Then

    For n = 1 to n

    CurrentValue = P*(1 + i)

                ‘set values of boxes here, e.g.:

                Me(“txtBoxRow” &  n & “Column1) = n

                Me(“txtBoxRow” &  n & “Column2) = P

                Me(“txtBoxRow” &  n & “Column3) = i

                Me(“txtBoxRow” &  n & “Column4) = CurrentValue

    P = CurrentValue

    n = n + 1  

    Next n

    End If

    End Sub

     

    Thank you,

    Mark

    Monday, April 18, 2016 5:20 AM

Answers

  • How about using a function like this to populate a temp table BEFORE you open the report (for example in a button click event to call the function to populate the table and open the report)?  Then you could simply set the bind your report to the temp table, and a single row of n,p,I and currentValue textboxes in the design of the detail section would display all values. 

    The code is not complete... you need to somehow supply values for the initial n,p and I (for example by adding parameters to pass to the function), but this should be enough to get you started.

    

    Dim n As Integer Dim P As Currency Dim i As Double Dim CurrentValue As Currency Dim intCount dim strSQL as string If n > 0 Then For intCount = 1 to n CurrentValue = P*(1 + i) strSQL = "INSERT INTO YourTable(n,p,i,CurrentValue) VALUES(" & intCount & "," & P & "," & I & "," & CurrentValue & ")" CurrentDB.Execute strSQL, dbfailonerror

    p = CurrentValue Next intCount End If



    Miriam Bizup Access MVP


    • Edited by mbizup MVP Monday, April 18, 2016 2:24 PM
    • Marked as answer by mdavisfps Monday, April 18, 2016 9:54 PM
    Monday, April 18, 2016 2:21 PM
  • I can't understand why you are using an unbound report when you can so easily use a bound report based on a query with far more flexibility.  With the Counters table:

    CREATE TABLE Counters
        (Counter LONG,
         CONSTRAINT PrimaryKey PRIMARY KEY (Counter));

    You can then either use parameters as in my first example, or you can store the values in rows in a table, e.g.

    CREATE TABLE Mortgages
        (MortgageID COUNTER,
         InitialAmount CURRENCY NOT NULL,
         AnnualRate DOUBLE NOT NULL,
         Term INTEGER NOT NULL,
         CONSTRAINT PrimaryKey PRIMARY KEY (MortgageID));

    And then include both tables in the query:

    SELECT MortgageID,Counter AS Year,
    FORMAT(InitialAmount*((1+(AnnualRate/100))^(Counter-1)),"Currency") AS [Initial amount],
    FORMAT(AnnualRate/100,"Percent") AS [Annual rate of interest],
    FORMAT(InitialAmount*((1+(AnnualRate/100))^Counter),"Currency") AS [Accumulated value]
    FROM Counters,Mortgages
    WHERE Counter <= Term
    ORDER BY MortgageID,Counter;

    With the following rows in Mortgages for instance:

    MortgageID    InitialAmount    AnnualRate    Term
    1                  £100.00           7                 3
    2                  £200.00           5                 5
                

    The query would return the following result table:

    MortgageID    Year    Initial amount    Annual rate of interest    Accumulated value
    1                 1         £100.00           7.00%                          £107.00
    1                 2         £107.00           7.00%                          £114.49
    1                 3         £114.49           7.00%                          £122.50
    2                 1         £200.00           5.00%                          £210.00
    2                 2         £210.00           5.00%                          £220.50
    2                 3         £220.50           5.00%                          £231.53
    2                 4         £231.53           5.00%                          £243.10
    2                 5         £243.10           5.00%                          £255.26

    In reality Mortgages would include other columns of course and you could open the report filtered to one or more mortgages as desired.

    Ken Sheridan, Stafford, England

    • Marked as answer by mdavisfps Monday, April 18, 2016 9:54 PM
    Monday, April 18, 2016 9:15 PM

All replies

  • Hi,

    It seems like a calculation of compound interest(composed interest).
    The below is an image of Excel, instead of Access, but I suppose it would be some help.


    I suppose that loop is not required, current value can be got directly.
    p * (i ^ n)

    Regards.

    • Edited by Ashidacchi Monday, April 18, 2016 7:31 AM
    Monday, April 18, 2016 7:12 AM
  • Yes, thanks for the interest, Ashidacchi. That's exactly what I want, but in an Access report.

    Mark

    Monday, April 18, 2016 12:41 PM
  • How about using a function like this to populate a temp table BEFORE you open the report (for example in a button click event to call the function to populate the table and open the report)?  Then you could simply set the bind your report to the temp table, and a single row of n,p,I and currentValue textboxes in the design of the detail section would display all values. 

    The code is not complete... you need to somehow supply values for the initial n,p and I (for example by adding parameters to pass to the function), but this should be enough to get you started.

    

    Dim n As Integer Dim P As Currency Dim i As Double Dim CurrentValue As Currency Dim intCount dim strSQL as string If n > 0 Then For intCount = 1 to n CurrentValue = P*(1 + i) strSQL = "INSERT INTO YourTable(n,p,i,CurrentValue) VALUES(" & intCount & "," & P & "," & I & "," & CurrentValue & ")" CurrentDB.Execute strSQL, dbfailonerror

    p = CurrentValue Next intCount End If



    Miriam Bizup Access MVP


    • Edited by mbizup MVP Monday, April 18, 2016 2:24 PM
    • Marked as answer by mdavisfps Monday, April 18, 2016 9:54 PM
    Monday, April 18, 2016 2:21 PM
  • Also, if you need to display the final values in a separate summary, with the above method you can use the report footer and aggregate functions by setting the control sources of textboxes to:

    = MAX(P)

    = MAX(CurrentValue)

    etc


    Miriam Bizup Access MVP

    Monday, April 18, 2016 2:31 PM
  • If you first create a table Counters with a single column Counter containing values from 1 to 100 say, then you can do it with the following query:

    PARAMETERS [Initial amount:] CURRENCY,
    [Annual rate of interest:] DOUBLE,
    [Number of years:] SHORT;
    SELECT Counter AS Year,
    [Initial amount:] AS [Initial amount],
    FORMAT([Annual rate of interest:]/100,"Percent") AS [Annual rate of interest],
    FORMAT([Initial amount:]*((1+([Annual rate of interest:]/100))^Counter),"Currency") AS [Accumulated value]
    FROM Counters
    WHERE Counter <= [Number of years:]
    ORDER BY Counter;

    The initial amount, rate of interest (as 7, not 0.07) and the number of years are input at runtime, so you can use the query for any permutation of these parameters.

    Ken Sheridan, Stafford, England

    Monday, April 18, 2016 3:56 PM
  • PS:  if you want to carry the accumulated values forward as in your original example:

    PARAMETERS [Initial amount:] CURRENCY,
    [Annual rate of interest:] DOUBLE,
    [Number of years:] SHORT;
    SELECT Counter AS Year,
    FORMAT([Initial amount:]*((1+([Annual rate of interest:]/100))^(Counter-1)),"Currency") AS [Initial amount],
    FORMAT([Annual rate of interest:]/100,"Percent") AS [Annual rate of interest],
    FORMAT([Initial amount:]*((1+([Annual rate of interest:]/100))^Counter),"Currency") AS [Accumulated value]
    FROM Counters
    WHERE Counter <= [Number of years:]
    ORDER BY Counter;

    Ken Sheridan, Stafford, England

    Monday, April 18, 2016 5:49 PM
  • First, thanks to all the responders. Sure appreciate the effort you went to.

    Please advise me if I'm missing some potential "trap", but the following does exactly what I want. Not without frustration, however, until I figured out that the unbound controls stayed at the initial value of the first row because I had Dim i as Integer, instead of Double. P*(1 + 0) is much different than P*(1.07)!

    What works:

    Dim n As Integer

    Dim i As Double

    Dim P As Currency

    Dim Leftside As Integer
           ‘from a previous array separating whole years from decimal portion

     

    n = LeftSide

    i = 0.07
     

    If n > 0 Then

    For n = 1 To n

        Me("txtR" & n & "C1") = n   

        Me("txtR" & n & "C2") = P   

        P = P * (1 + i)
        Me("txtR" & n & "C3") = P       

    Next n 

    End If

    Mark

    Monday, April 18, 2016 8:25 PM
  • I can't understand why you are using an unbound report when you can so easily use a bound report based on a query with far more flexibility.  With the Counters table:

    CREATE TABLE Counters
        (Counter LONG,
         CONSTRAINT PrimaryKey PRIMARY KEY (Counter));

    You can then either use parameters as in my first example, or you can store the values in rows in a table, e.g.

    CREATE TABLE Mortgages
        (MortgageID COUNTER,
         InitialAmount CURRENCY NOT NULL,
         AnnualRate DOUBLE NOT NULL,
         Term INTEGER NOT NULL,
         CONSTRAINT PrimaryKey PRIMARY KEY (MortgageID));

    And then include both tables in the query:

    SELECT MortgageID,Counter AS Year,
    FORMAT(InitialAmount*((1+(AnnualRate/100))^(Counter-1)),"Currency") AS [Initial amount],
    FORMAT(AnnualRate/100,"Percent") AS [Annual rate of interest],
    FORMAT(InitialAmount*((1+(AnnualRate/100))^Counter),"Currency") AS [Accumulated value]
    FROM Counters,Mortgages
    WHERE Counter <= Term
    ORDER BY MortgageID,Counter;

    With the following rows in Mortgages for instance:

    MortgageID    InitialAmount    AnnualRate    Term
    1                  £100.00           7                 3
    2                  £200.00           5                 5
                

    The query would return the following result table:

    MortgageID    Year    Initial amount    Annual rate of interest    Accumulated value
    1                 1         £100.00           7.00%                          £107.00
    1                 2         £107.00           7.00%                          £114.49
    1                 3         £114.49           7.00%                          £122.50
    2                 1         £200.00           5.00%                          £210.00
    2                 2         £210.00           5.00%                          £220.50
    2                 3         £220.50           5.00%                          £231.53
    2                 4         £231.53           5.00%                          £243.10
    2                 5         £243.10           5.00%                          £255.26

    In reality Mortgages would include other columns of course and you could open the report filtered to one or more mortgages as desired.

    Ken Sheridan, Stafford, England

    • Marked as answer by mdavisfps Monday, April 18, 2016 9:54 PM
    Monday, April 18, 2016 9:15 PM
  • Thanks, Ken. I understand what you're suggesting and it makes sense to me. However, on another level, what you're suggesting is so much above my skill level that it would be quite difficult for me. (I spent several hours, yesterday, just trying to remember how to phrase a WHERE clause).

    I've marked both yours and Miriam's as "answers". I think she suggested basically the same thing I was doing, only she wrote the data to a table first, and then used that as the record source. I guess that's essentially what you're suggesting, too, right?

    What's the value of doing that, versus writing directly(?) to the controls via the report's On Activate event? (I'm guessing there is some value.)

    Probably not much difference than a mortgage computation, but mine is for showing a particular individual how we came up with what we did regarding how much they owed on their lien.

    Now, on to learning how to toggle the Visible property of a select group of controls on a report from False to True, and vice versa. (Well, there's one value of doing it your and Miriam's way, right?)

    Thanks, again.

    Mark

    Monday, April 18, 2016 10:09 PM
  • <<What's the value...>>

    Design simplicity and flexibility (with either the temp table or Ken's excellent counter table/query suggestion).  

    If I'm understanding your initial approach correctly, you'd have to create a grid of nine controls for the three years you are reporting.

    Now if your clients decide they need a fourth year reported, you'd have to modify the design to include a fourth row of controls (repeat each time they want to change this).

    The advantages of the BOUND reports we are suggesting... 

    you could use a simple user entry form to allow them to specify any of the parameters (initial p, i, cv, rate or number of years), and the detail section would automatically expand according to the rows of the query or table with no design changes needed and no additional code necessary to toggle visibility of rows.

    Additionally, a bound report allows you to SIMPLY summarize data.  You can use aggregate functions in the report header and footer sections, such as

    MAX(CV)

    AVG(CV)

    etc.

    Those calculations would be a lot more complex in an unbound report.

    

    Miriam Bizup Access MVP




    • Edited by mbizup MVP Tuesday, April 19, 2016 8:56 AM
    Tuesday, April 19, 2016 8:53 AM
  • Thanks, Miriam. Point taken.

    FYI (and this probably just further supports your and Ken's suggestions), but the report actually has 60 unbound text boxes, so it's good to go for 20 years. I don't expect that to be a problem, given the nature of the liens, but should it ever occur I would just add another row of boxes on the report, but without any adjustment to the code required.

    Still, thanks to you and Ken showing me how it should have been done. I'd already started down one path and kind of wanted to finish that. Take care.

    Mark

    Tuesday, April 19, 2016 5:55 PM
  • Hi, mdavisfps

    I am glad to hear that you have clue to resolve your issue. if you have done, you could share your solution, that will help other community members when they have same issue.

    Thanks for your understanding.
    Friday, April 22, 2016 5:23 AM