DIRECTIONS FOR USING REBATE SPREADSHEET
The spreadsheet automatically calculates the net savings to the government and determines whether an agency should pay early or on the Prompt Payment due date. In order to use this spreadsheeet, please follow the directions below:
1. Enter the amount of money owed to credit card provider in cell C3.
2. Enter the maximum discount offered by the credit card provider in cell E3. 3. Enter the basis points offererd by the credit card provider in cell F3. 4. After this data is entered, compare the net savings if payment was made as early as possible (cell G6) with the amount of money the government would save if payment was made as late as possible (cell G36). If the net savings in cell G6 exceeds the net savings in cell G36, it may be in the best interest of the government to accept the rebate and pay as early as possible. However, if the net savings in cell G6 is less than the net savings in cell G36, it may be in the best interest of the government to pay as close to the Prompt Payment due date as possible. An example for using this spreadsheet is also available.
EXAMPLE FOR USING REBATE SPREADSHEET
Maximum discount offered by card provider: 1.070% Basis points offered by credit card provider: .016%, or 1.6 Current Value of Funds (CVF) Rate in Effect: 6%
Amount of money owed to the vendor: $5,000 Net savings if payment is made as early as possible: $28.50 (Cell G6)
Net savings if payment is made on Prompt Payment due date: $29.50 (Cell G36) * In this example, it would be in best interest of the government if payment was made as close to the payment due date as possible.
Current Value of Funds 6.00%
Enter Amount of Debt $5,000.00
Enter Maximum Discount 1.060% IR Cost of $ Cost of Borrowing Borrowing Balance Balance 0.50% $25.00 0.48% $24.17 0.47% $23.33 0.45% $22.50 0.43% $21.67 0.42% $20.83 0.40% $20.00 0.38% $19.17 0.37% $18.33 0.35% $17.50 0.33% $16.67 0.32% $15.83 0.30% $15.00 0.28% $14.17 0.27% $13.33 0.25% $12.50 0.23% $11.67 0.22% $10.83 0.20% $10.00 0.18% $9.17 0.17% $8.33 0.15% $7.50 0.13% $6.67 0.12% $5.83 0.10% $5.00 0.08% $4.17 0.07% $3.33 0.05% $2.50 0.03% $1.67 0.02% $0.83 0.00% $0.00
Enter Basis Points 0.0150%
Days to Maturity Perc Disc 30 1.060% 29 1.045% 28 1.030% 27 1.015% 26 1.000% 25 0.985% 24 0.970% 23 0.955% 22 0.940% 21 0.925% 20 0.910% 19 0.895% 18 0.880% 17 0.865% 16 0.850% 15 0.835% 14 0.820% 13 0.805% 12 0.790% 11 0.775% 10 0.760% 9 0.745% 8 0.730% 7 0.715% 6 0.700% 5 0.685% 4 0.670% 3 0.655% 2 0.640% 1 0.625% 0 0.610%
$ Disc $53.00 $52.25 $51.50 $50.75 $50.00 $49.25 $48.50 $47.75 $47.00 $46.25 $45.50 $44.75 $44.00 $43.25 $42.50 $41.75 $41.00 $40.25 $39.50 $38.75 $38.00 $37.25 $36.50 $35.75 $35.00 $34.25 $33.50 $32.75 $32.00 $31.25 $30.50
Balance $4,947.00 $4,947.75 $4,948.50 $4,949.25 $4,950.00 $4,950.75 $4,951.50 $4,952.25 $4,953.00 $4,953.75 $4,954.50 $4,955.25 $4,956.00 $4,956.75 $4,957.50 $4,958.25 $4,959.00 $4,959.75 $4,960.50 $4,961.25 $4,962.00 $4,962.75 $4,963.50 $4,964.25 $4,965.00 $4,965.75 $4,966.50 $4,967.25 $4,968.00 $4,968.75 $4,969.50
Net Savings $28.00 $28.08 $28.17 $28.25 $28.33 $28.42 $28.50 $28.58 $28.67 $28.75 $28.83 $28.92 $29.00 $29.08 $29.17 $29.25 $29.33 $29.42 $29.50 $29.58 $29.67 $29.75 $29.83 $29.92 $30.00 $30.08 $30.17 $30.25 $30.33 $30.42 $30.50