Setting a depreciation schedule

[Rangoon]

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

[comment]

1. Are these percentages constant for all records?

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

[Rangoon]

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.

[Rangoon]

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.

[comment]

I hope this helps.

 

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

 

 

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 )

[Rangoon]

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

[comment]

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

[David Jondreau]

Now that is some amazing community support!

 

Excellent solution!

 

[Rangoon]

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

[comment]

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).

[LaRetta]

Awesomating ... as always ... 

 

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.

[comment]

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?

[LaRetta]

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'

[Rangoon]

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!

[Rangoon]

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.

[comment]

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)

)

...

[Rangoon]

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!