SQL Server does a very strange stuff with precision of the result when doing basic math operations (*, /). I did a research and to put it short, use DECIMAL(25,13) type if you want to retain precision, or generally, DECIMAL(p,s) where p+s = 38!
REAL is not a precise type, it's a floating point type.
If you return money, be aware that you actually rounded the result to 4 decimal places.
So, you should probably go with something like this:
CREATE FUNCTION [dbo].VAT (
@Net_Amount [money],
@Order_Date [date] )
RETURNS money
AS
BEGIN
DECLARE @VAT_Rate DECIMAL(25,13) =
CASE
WHEN @Order_Date >= '2011-01-04' then 0.200
WHEN @Order_Date >= '2010-01-01' then 0.175
WHEN @Order_Date >= '2008-12-01' then 0.150
WHEN @Order_Date >= '1991-01-01' then 0.175
WHEN @Order_Date >= '1979-06-18' then 0.150
WHEN @Order_Date >= '1974-07-01' then 0.080
WHEN @Order_Date >= '1973-04-01' then 0.100
ELSE 0.000
END
RETURN CONVERT( DECIMAL(25,13), @Net_Amount ) * @VAT_Rate
END
GO