Reserve Formula

[kumar1803]

Hello Guys,

I work for a auto dealership and my boss asked me to calculate a reserve on finance deals. I have total 5 finance deals and total amount of financed is: $169,819. My spread is always 2% ( buy rate-sell rate)

The reserve is the difference between buy rate and sell rate. How do I calculate my reserve assuming my spread is always 2%.

Number of finance deal: 5
Total Amount financed: $169,819
Amount of reserve: ?

Assuming 2% is the spread which is difference between sell rate and buy rate.

 

[Biz]

Is is correct assume Amount of reserve= Total Amount financed*(1+Spread%)?

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules. However, for the future, you might note point 24 of our Posting Guidelines.

You might understand that some helpers may become disgruntled if they put time into developing a solution here only to find later that a solution had already been achieved elsewhere. They may then be less likely to help you next time.

You may also want to consider whether cross-posting actually increases your chance of getting a quick answer. Some (but certainly not all) members, because of the point above, tend to skip questions that they know are cross-posted because they don’t want to bother going to the other forum(s) to check if it has already been solved. It can also become rather messy trying to keep up if the threads are active in multiple forums at the same time.

Cross posted at: [url]http://www.excelforum.com/excel-formulas-and-functions/1114582-excel-formula-payment-calculation.html[/URL]

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.

 

[kumar1803]

Is is correct assume Amount of reserve= Total Amount financed*(1+Spread%)?

Thank you for a quick response:

I am still not getting the correct amount that way.

What if the average loan term is 6 years ( 72 month)

How much would the amount of reserve should be?

 

[kumar1803]

Thanks for letting me know. will follow rule.

 

[kumar1803]

What if the following change in the question above:

Amount Financed: $169,819
Spread 2% (Sell Rate-Buy Rate)
Term of Loan=6 years
What is the total payment amount?

 

[konew1]

The payments can only be worked out from the actual interest rates, not from the spread. I'm not really sure what you mean by reserve, but I expect that the reserve can only be calculated from actual interest rates, not from the spread. see below, 3 examples with same loan amount, all 36 months, all with 2% spread but with no correlation between the interest received from vehicle sales and the interest expense paid to your finance company

To answer the question in posting #6 - you should look at the =PMT() formula

Excel Workbook
	A	B	C	D	E	F	G	H	I
2		Buy	Sell		Buy	Sell		Buy	Sell
3		36	36		36	36		36	36
4		5%	7.00%		7%	9.00%		9%	11.00%
5	$ 169,819
6	Payments (monthly)	$5,089.63	$5,243.52		$5,243.52	$5,400.20		$5,400.20	$5,559.66
7	Total Payments	$183,226.60	$188,766.64		$188,766.64	$194,407.16		$194,407.16	$200,147.62
8	Interest cost/income	$13,407.60	$18,947.64		$18,947.64	$24,588.16		$24,588.16	$30,328.62
9
10	interest difference		$5,540.04			$5,640.52			$5,740.47
Sheet3