Jump to content
View in the app

A better way to browse. Learn more.
FMForums.com

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

Setting a depreciation schedule

Rangoon
in Calculation Engine (Define Fields)

Featured Replies

I’ve been trying to figure this out and not ending up where I want to be.  Here’s my dilemma.

 

I need to set up a depreciation schedule based on known percentages that look like this.

 

 

YEAR = Entry field of year

MSRP = Entry field of $ MSRP (this field and all following fields are $ amounts)

SDISC = This is a 35% automatic discount off of MSRP 

YR1 = This is an 18% depreciation the 1st year  

YR2 = This is a 10% depreciation the 2nd year 

YR3 = This is a 7% depreciation the 3rd year

YR4 = This is a 6% depreciation the 4th year

YR5 = This is a 6% depreciation the 5th year

YR6 = This is a 5% depreciation the 6th year

YR7 = This is a 5% depreciation the 7th year

YR8 = This is a 4% depreciation the 8th year

YR9 = This is a 4% depreciation the 9th year

YR10 = This is a 3% depreciation the 10th year

YR11 = This is a 3% depreciation the 11th year

YR12 = This is a 2% depreciation the 12th year

YR13 = This is a 2% depreciation the 13th year

 

And here’s a kicker, Whatever the current year is on the depreciation schedule the $ amounts in the field needs to be in Yellow.  So if 2014 is in the “YEAR” field and the current year is 2016, the $ amount shown in YR2 needs to be in yellow for ease of viewing.

 

Any help will be appreciated

1. Are these percentages constant for all records?

2. How does the discount play in this? Perhaps you could post a worked-out example.

  • Author

Hi Comment,  Yes all of the percentages are constant for all records.  The discount is the common discount applied to each unit regardless of MSRP.  I'll post a worked-out example as you requested.  Thank you for your hep.

  • Author

Hi Comment, I hope this helps.

 

Year 2014

 

MSRP $365,700.00 

SDISC $237,900.00   ($ 365,700.00 - 35% of MSRP $127,800.00)

YR1     $195,078.00      (SDISC $237,900 - 18% of Yr1 $42,822.00)

 

And the same basis is used for each proceeding year.

I hope this helps.

 

It would help considerably more if it were correct... I believe that 35% of 365,700 is 127,995.  :no:

 

 

Anyway, you could make this short and sweet by defining a calculation field =

Let ( [
trigger =  Extend ( YEAR ) ; //this is optional; ensures refresh of conditional formatting when YEAR is modified.
d = Choose ( Get ( CalculationRepetitionNumber ) - 1 ; 
1 - .35 ;
( 1 - .35 ) * ( 1 - .18 ) ;
( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) ;
( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) ;
( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 )
//...
)
] ;
Extend ( MSRP ) * d
)

Set the result type to Number and the number of repetitions to 14.

 

(Of course, you could replace the multiplications with their results, ending up with a more compact, albeit less understood formula.)

 

 

Format the field conditionally to highlight when =

Min ( Year ( Get ( CurrentDate ) ) - YourTable::YEAR + 1 ; 14 ) = Get ( CalculationRepetitionNumber )
    •
    • Like 2
    • Author

    Hi Comment,

     

    First let me thank you for your help.  The calculation is working but of the 14 repetition Fields only repetition 1 thru 5 are filled in with the correct numbers, the repetition 6 thru 14 are empty.  

     

    I don’t know where to “format the field conditionally to highlight”. 

    Min ( Year ( Get ( CurrentDate ) ) - YourTable::YEAR + 1 ; 14 ) = Get ( CalculationRepetitionNumber )

     

    Sorry about my math error on my earlier post, I should have double checked it.

    Am I correct that I do not need fields Yr1 through Yr13?

     

    Thank you

    repetition 6 thru 14 are empty

     

    Well yes, of course they are: you need to continue the calculation along the lines shown in my example. That's what the

    //...

    part indicates.

     

     

    I don’t know where to “format the field conditionally to highlight”.

     

    Place the calculation field on the layout, set it to show all 14 repetitions, then follow the instructions for specifying conditional formatting based on a calculation here:

    http://www.filemaker.com/help/13/fmp/en/html/edit_layout.10.13.html#1066700

    Now that is some amazing community support!

     

    Excellent solution!

     

    :worship:

    • Author

    Thanks so much Comment,

     

    I had figured out how to set up the conditional field prior to your post but the Calculation was a different thing.  I tried to follow what you did and was getting nowhere but frustrated.  I was just ready to come back and plead for more help when I dawned on me and here's it is.

     

    Let ( [

    trigger =  Extend ( Yr ) ; //this is optional; ensures refresh of conditional formatting when YEAR is modified.
    d = Choose ( Get ( CalculationRepetitionNumber ) - 1 ; 
    1 - .35 ;
    ( 1 - .35 ) * ( 1 - .18 ) ;
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) ;
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) ;
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 )
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06)
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05)
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05) * (1 - .05)
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05) * (1 - .05) * (1 - .04)
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05) * (1 - .05) * (1 - .04) * (1 - .04)
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05) * (1 - .05) * (1 - .04) * (1 - .04) * (1 - .03)
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05) * (1 - .05) * (1 - .04) * (1 - .04) * (1 - .03) * (1 - .03)
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05) * (1 - .05) * (1 - .04) * (1 - .04) * (1 - .03) * (1 - .03) * (1 - .02)
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05) * (1 - .05) * (1 - .04) * (1 - .04) * (1 - .03) * (1 - .03) * (1 - .02) * (1 - .02))
     
    //...
    ] ;
    Extend ( MSRP ) * d
    )
     
    I know this may be old hat for you guys but I must admit I did a little "Mexican Hat dance"!
     
    Thank you very much
    I must admit I did a little "Mexican Hat dance"!

     

    I am glad for you, but the syntax of what you posted is wrong. You must have a semicolon after each result inside the Choose() statement, except the last one. (And you can remove the //... comment).

    Awesomating ... as always ...  :clap:

     

    Yeah, ahm hey Michael? I need to build a rocket for a moon shot so can you go ahead and calculate the trajectory (Jul 1 2014 lift off) for me so I can track it in a table?  BAM!  There ya go ... compliments of Comment on FMForums!

     

    Question:  Would those percentage rates ever change?  I would think so since everything does change.  Might it be helpful to set them in repetition in Preferences table (or even a table of records with a RateExpirationDate so prior records remain correct if the rate changes?  Also there would be a lot of manual changes to make in that single calc, not only wasting time but opening possibility of user/Developer typing error.  Just some considerations.

    Question:  Would those percentage rates ever change?

     

    That is a valid concern. I believe that was my first question here. I think it's very unlikely that the rates would change retroactively for existing records - so if they are "constant for all records", it is implied that they are permanent too. Otherwise you'd need to look them up into each record (perhaps using the initial year as the matchfield for the lookup).

     

    As I said, this is "short and sweet" - which is just another term for "quick and dirty".

     

     

    I need to build a rocket for a moon shot so can you go ahead and calculate the trajectory

     

    Isn't there something in the EULA that prohibits the use of Filemaker in space flight?

    Isn't there something in the EULA that prohibits the use of Filemaker in space flight?

     

    And again you are correct ... at least in MY version of the EULA.  Well, if I DID need that calculation, I know where I'd head ... just sayin'

    • Author

    Yes the percentages are constant and will not change over time. If that calculation was "quick and dirty" I don't think I want to see "slow and clean" wow!

    • Author

    Hi again Comment,

     

    I went back to my calculation after receiving your post but I can't figure out exactly what the  "You must have a semicolon after each result inside the Choose() statement, except the last one." means or where I would put the semicolons.  

     

    Since the calculation I have works, is there any reason to change it?

     

    Thanks again for your help.

    Since the calculation I have works

     

    Well, I was all set to reply that's not possible, but ... congratulations, you have discovered a bug. And it seems this bug was with us for quite some time - probably since version 7.

     

    So my answer is that your calculation works, though it shouldn't. And I would recommend that you fix it to conform to the published specification of the Choose() function, that requires (just like any other function) a semicolon delimiter between the function arguments.

     

     

    where I would put the semicolons

     

    ...

    d = Choose ( Get ( CalculationRepetitionNumber ) - 1 ;
    1 - .35 ;
    ( 1 - .35 ) * ( 1 - .18 ) ;
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) ;
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) ;
    ...
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05) * (1 - .05) * (1 - .04) * (1 - .04) * (1 - .03) * (1 - .03) ;
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05) * (1 - .05) * (1 - .04) * (1 - .04) * (1 - .03) * (1 - .03) * (1 - .02) ;
    ( 1 - .35 ) * ( 1 - .18 ) * ( 1 - .1 ) * ( 1 - .07 ) * ( 1 - .06 ) * (1 - .06) * (1 - .05) * (1 - .05) * (1 - .04) * (1 - .04) * (1 - .03) * (1 - .03) * (1 - .02) * (1 - .02)
    )
    ...
    • Author

    Hi Comment,

     

    RE" congratulations, you have discovered a bug

     

    I always new I had some developer genes in me somewhere.  I'm sure that Filemaker will want to enrich my life with a check for "Services Rendered" and I want you to know whatever the amount, there will be a 60/40 split.  Yes, you get the 60 as its only right to yield to brilliance!

    Create an account or sign in to comment

    https://fmforums.com/topic/92379-setting-a-depreciation-schedule/
    Go to topic listing

    Important Information

    By using this site, you agree to our Terms of Use.

    I accept

    Configure browser push notifications
    Chrome (Android)
    1. Tap the lock icon next to the address bar.
    2. Tap Permissions → Notifications.
    3. Adjust your preference.
    Chrome (Desktop)
    1. Click the padlock icon in the address bar.
    2. Select Site settings.
    3. Find Notifications and adjust your preference.