Question

Sales Manager has an excel workbook listing properties for sale. If a property is sold, Select National Properties Group will finance the property loan for 30 years as 7.5% interest. If the monthly payment of a property is greater than $3000,000 the company will give the buyer a 1% discount on the selling price.

Assignment:

Calculated the column using the PMT Function to calculate the monthly payment for each of the properties. Then insert a calculated column using the IF function to determine the amount of the discount if the property qualifies for it.

I need the formulas in excel

Property	Address	City	State	ZIP	Selling Price	Month Payment	Discounts
Albuquerque Technical Training	789 W. Main St.	Albuquerque	NM	87110	$        604,500
Alvarado Surgical Center	106 E. 1500 N.	Rugby	ND	58368	$ 53,999,000
American Service Industry Fulfillment	1401 Mother Lode Cir.	Port Huron	MI	48060	$        751,500
Arbor Square Shopping Mall	4980 Hwy. 14 E	St. Louis	MO	63122	$    6,390,000
Arden Wood Medical Center	306 W. Central Ave.	Arlington	TX	76012	$ 68,433,000
Arizona Surgical Hospital	42 W. 35th Ave.	Ulysses	KS	67880	$ 55,500,000
Arroyo Grande Rehabilitation Hospital	500 NW Cheyenne	Salem	NH	03079	$ 75,520,000
Aston Office Solutions Warehouse	2211 SE 16 St.	Battle Creek	MI	49017	$    6,230,000
Austin & Ball Warehouse	712 E. 108th Ave.	Paterson	NJ	07514	$    9,189,500
Ballyhoo Office Building	4161 N. 1500 W.	Green Bay	WI	54305	$    5,675,000
Barton, Oaks & Colina Building	1119 NE Thorn Hill Rd.	Jefferson City	MO	65101	$    5,460,000
Belmar Group Warehouse	30 Imperial Way	Bolivar	TN	38008	$    4,900,000
Bergstrom Industries Warehouse	209 Elice Cir.	Littleton	CO	80122	$    7,326,000
Best Electronics Manufacturing	8692 Technology Blvd.	Berkeley	MI	48072	$    6,030,000
Bloomington Hospital	843 Fayette Ave.	Amarillo	TX	79106	$ 60,450,000
Boulder Community Training Services	176 Van Buren St.	Boulder	CO	80026	$        848,900
Broaddus Surgical Center	145 Tudor Dr.	Waterloo	IA	50702	$ 68,150,000
Broatman Hospital	22161 N. Randolph Dr.	Auburn	WA	98001	$ 47,146,000
Builder Supply Warehouse	103 Cleveland St.	Elkhorn	WI	53121	$    6,412,000
Cardman Facility	100 Linden Ln.	Rock Springs	WY	82901	$    3,980,000
Casa Mesa Regional Medical Center	926 Camino Blvd.	Inglewood	CA	90301	$ 81,950,000
Cayeta County Educational Services	6921 1st Ave. W.	Brookfield	WI	53045	$        597,500

Answer 1

The monthly payment is calculated in excel using PMT formula

= - PMT(rate, nper, pv, [fv], [type])

here,

rate is rate of interest 7.5% given in question, we will divide it by 12 so that it is expressed as a monthly rate

nper is the no. of periods, i.e.30 years, we will multiply it by 12, as we want to return a monthly payment, we express this argument in months

pv, is the amount of the loan, or, present value.

fv is the optional argument for future value, here we leave it blank

type is an optional argument to define when the payment occurs, here we leave it blank

we have put a minus sign before pmt function like this

= - pmt

so as to get the monthly payment in positive

so now the formula for monthly payment will be

=-pmt(7.5/12,30*12,the selling price of every property)

this has given us the monthly payment for each property (stated in the table below)

Then a IF function is used to see if discount is eligible or not, hence if the monthly payment is greater than 3,000,000. the discount eligible column return as 1 else 0

if this column return as 1 (i.e., discount is eligible) then discount is calculated in the next column as 1% of selling price

The answers calculated in this manner are shown in the table below:

annual interest rate		7.5
terms in years		30