SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

decimal places in depreciation

decimal places in depreciation

Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 19
Okay, so I've been working off and on on a project aimed at calculating depreciation of assets for companies.

I've got the logic down, or at least I think I do, but my numbers are coming up between a few dollars and a few cents off of what they should be ( as calculated in Microsoft Excel 2007). I've got a feeling that it's got to do with how my code is handling decimal places, but I don't really know what's wrong.

I have two functions that I am struggling with.

The first calculates the rate of depreciation as such.

CREATE FUNCTION rateOfDecline(@numMonths as decimal(10,4))

RETURNS decimal(10,4)


RETURN ((1/@numMonths)*2)


My problem here is that I'm limiting the decimal places to 4, (to be equivalent to the number of decimals stored in the money variable, which I'm using for the next function). This means that If I input say 96 months as the period, the result is .0208333333etc, but comes up as .0208 (rounded down) when that first three is an important number to retain. Again I don't need a solution to just this case, but something that will be able to detect which decimals are important vs. which are not.

I feel like my problem lies more in the rate function than this part, but I'm not totally sure.

My other function uses a loop to compute the amount of decline for any given month in the period.

it looks like this:

CREATE FUNCTION dbo. calcDoubleDeclineGivenMonth(@countableMonthTotal AS int, @purchasePrice AS money,

@rateOfDoubleDecline AS decimal(10, 4))



DECLARE @valueThisMonth AS money, @valuePreviousMonth AS money, @amountDeclinedThisMonth AS money,

@countMonths AS int

/*sets the counter to "0"*/

SET @countMonths = 0

/*sets the initial value equal to the purchase price*/

SET @valueThisMonth = @purchasePrice

/*While countMonths is less than the total number of

months to calculate...*/

WHILE @countMonths < @countableMonthTotal


/*sets the value of the previous month equal to the current month*/

SET @valuePreviousMonth = @valueThisMonth

/*calculates amount declined in current month by multiplying the rate

of decline against the previous month's value*/

SET @amountDeclinedThisMonth = @valuePreviousMonth * @rateOfDoubleDecline

/*sets the new value of the asset by subtracting the amount declined*/

SET @valueThisMonth = @valuePreviousMonth - @amountDeclinedThisMonth

/*adds one month to the counter and repeats the loop until countMonths = countableMonthTotal*/

SET @countMonths = @countMonths + 1


RETURN @amountDeclinedThisMonth



In all reality I'm a novice at this stuff, so If my way of doing things is a bit strange I apologize and would appreciate any advice in the vein of constructive criticism, but really I just need the numbers to add up X_X;.




You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum