Question

how doni do this in excel and what is the interest rate?

Suppose that you wish to purchase a car and that your bank is offering to you a loan. You wish to explore the nature of this loan and the payments that you would have to make given certain circumstances such as the amount that you borrow. Fortunately, Excel offers a function (PMT) that calculates the payment for a loan based on constant payments and a constant interest rate. The syntax of the function is: PMT(rate, nper, pv) where rate is the interest rate for the loan, nper is the total number of payments for the loan, and pv is the amount of the loan. Create a spreadsheet where you may enter the three parameters and have the payment be calculated for you. Once you have done this, create a single variable table that will list for you the different payments given different amounts of money borrowed (assume you are considering buying a car in the S10,000 to $20,000 range looking at S1000 increments). Also on this tab solve the following double variable problem: Create a double variable table that has amount borrowed as the column variable and number of years as the row variable and payment as the output variable. Also on this tab solve the following double variable problem: Create a double variable table that has interest rate as the column variable and number of years as the row variable and Payment as the output variable. (NOTE: Rather than use number of years, if you are making monthly payments, the value would be number of months (i.e., three years would be 36). Finally, create three scenes (high, medium, low) using Scenario for the problem stated above.

Answer 1

		Assume<
	Pv	Amount Borrowed	$10,000
		Annual interest rate	6%
	Rate	Monthly interest rate=6/12	0.50%
	Nper	Number of months of payment	48	(4Years*12)
	PMT	Monthly Constant Payment	$234.85	(Using PMT function with Rate=0.5%, Nper=48,Pv=-10000)
		Excel Command:PMT(0.5%,48,-10000)
		SINGLE VARIABLE TABLE:
		A	PMT	(Using PMT function with Rate=0.5%, Nper=48,Pv=-A)
		Amount Borrowed	Monthly Constant Payment
		$10,000	$234.85	Excel Command:PMT(0.5%,48,-10000)
		$11,000	$258.34	Excel Command:PMT(0.5%,48,-11000)
		$12,000	$281.82	Excel Command:PMT(0.5%,48,-12000)
		$13,000	$305.31	Excel Command:PMT(0.5%,48,-13000)
		$14,000	$328.79	Excel Command:PMT(0.5%,48,-14000)
		$15,000	$352.28	Excel Command:PMT(0.5%,48,-15000)
		$16,000	$375.76	Excel Command:PMT(0.5%,48,-16000)
		$17,000	$399.25	Excel Command:PMT(0.5%,48,-17000)
		$18,000	$422.73	Excel Command:PMT(0.5%,48,-18000)
		$19,000	$446.22	Excel Command:PMT(0.5%,48,-19000)
		$20,000	$469.70	Excel Command:PMT(0.5%,48,-20000)
		DOUBLE VARIABLE TABLE
		(Using PMT function with Rate=0.5%, Nper=N,Pv=-A)
			Number of Years
	A	Amount Borrowed	2	3	4	5	6
	N	(Number of months)	24	36	48	60	72
		$10,000	$443.21	$304.22	$234.85	$193.33	$165.73
		$11,000	$487.53	$334.64	$258.34	$212.66	$182.30
		$12,000	$531.85	$365.06	$281.82	$231.99	$198.87
		$13,000	$576.17	$395.49	$305.31	$251.33	$215.45
		$14,000	$620.49	$425.91	$328.79	$270.66	$232.02
		$15,000	$664.81	$456.33	$352.28	$289.99	$248.59
		$16,000	$709.13	$486.75	$375.76	$309.32	$265.17
		$17,000	$753.45	$517.17	$399.25	$328.66	$281.74
		$18,000	$797.77	$547.59	$422.73	$347.99	$298.31
		$19,000	$842.09	$578.02	$446.22	$367.32	$314.88
		$20,000	$886.41	$608.44	$469.70	$386.66	$331.46
		DOUBLE VARIABLE TABLE
		WITH INTEREST RATE AS COLUMN VARIABLE
		Borrowed amount	$10,000
		(Using PMT function with Rate=R, Nper=N,Pv=-10000)
			Months(N)
		R	24	36	48	60	72
	Annual Interest	Interest Rate (Monthly)
	3%	0.25%	$429.81	$290.81	$221.34	$179.69	$151.94
	4%	0.33%	$434.25	$295.24	$225.79	$184.17	$156.45
	6%	0.50%	$443.21	$304.22	$234.85	$193.33	$165.73
	8%	0.67%	$452.27	$313.36	$244.13	$202.76	$175.33
	10%	0.83%	$461.45	$322.67	$253.63	$212.47	$185.26
	12%	1.00%	$470.73	$332.14	$263.34	$222.44	$195.50