October 5, 2007 at 12:17 pm
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)
BEGIN
RETURN ((1/@numMonths)*2)
END;
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))
RETURNS money
BEGIN
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
BEGIN
/*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
END
RETURN @amountDeclinedThisMonth
END;
GO
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;.
Thanks
MHovde
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply