MAO

[Saher Naji]

Hello
I hoping you able to help me.
I am trying to calculate the Maximum Allowable Offer (MAO) which is=

[ARV] - [the preferred profit margin (I would want the ability to change this number)] - [the selling costs] - [the renovations costs] - [the wholesale fee] - [the holding costs]

The issue that I'm having is that the holding costs is calculated based on the MAO value and I don't know how to calculate the holding costs while I'm trying to figure out the MAO.

Take a look please,

Thanks a lot

Saher

Flip Calculator (1) (1).xlsx
	A	B	C	D	E	F	G	H
1	PROFIT ANALYSIS SHEET
2
3	Property Address
4
5	Estimated Sales Price (ARV)		$500,000.00		Property Details
6	Estimated Hold Time (months)		5		Property Square Footage	1441
7	Category	Costs	Total		Number of Bedrooms	3
8	BUYING				Number of Bathrooms	2
9	Offer to Seller	$277,000	$277,000.00		Added Square Footage	0
10	Wholesale Fee	$15,000	$15,000.00		MAXIMUM ALLOWABLE OFFER		$277,000.00
11	Closing Costs / Title Ins.	2%	$5,840.00
12	TOTAL ACQUISITION COST		$297,840.00
13
14	RENOVATION				Repair Estimate Key
15	Interior Repairs*	$57,640	$66,286.00		Type	Cost per Sq. Ft.	Rehab Cost
16	Exterior Repairs*	$5,000	$5,750.00		Lipstick	$20.00	$28,820.00
17	Monthly Maintenance (Pool, Landscape, HOA, etc.)	$50	$250.00		Basic	$40.00	$57,640.00
18	TOTAL RENOVATION COSTS		$72,286.00		Gut Job	$60.00	$86,460.00
19					Destroyed	$75.00	$108,075.00
20	HOLDING - Loan Amt (LTC)	80%	$221,600.00		Addition	$125.00	$0.00
21	Payment Required (Hard Money %)	11.00%	$13,469.78
22	Loan Arrangement and/or Points	1.00%	$2,770.00		TOTAL ESTIMATED PROFIT	$75,359.23
23	Private funds Interest		$0.00
24	Property Taxes (annual)	$1,500	$625.00		Down Payment	$55,400.00
25	Insurance (monthly)	$200	$1,000.00		Cash Requirements	$147,200.78
26	Utilities (monthly)	$300	$1,500.00		Profit Margin	15.07%
27	TOTAL HOLDING COSTS		$19,364.78
28
29	SELLING
30	Staging/Marketing/Photos/Etc.	$150	$150.00
31	Closing Costs, Concessions, BINSR	2.00%	$10,000.00
32	Seller Credit To Buyer	0	0
33	Sales Commission - Listing Agent	2.50%	$12,500.00
34	Sales Commission - Buyers Agent	2.50%	$12,500.00
35	TOTAL SELLING COSTS		$35,150.00
36
37	TOTAL PURCHASE, RENOVATION, HOLDING & SALES COSTS		$424,640.78
38
39	*Total includes 15% buffer
Sheet1

Cell Formulas
Range	Formula
G10	G10	=C9
C32,C30,C9:C10	C9	=B9
C11	C11	=(C9+C10)*B11
C12	C12	=SUM(C9:C11)
B15	B15	=G17
C15:C16	C15	=(B15)*1.15
C17	C17	=B17*C6
C18	C18	=C15+C16+C17
G16	G16	=F16*F6
G17,G20	G17	=F17*F6
G18	G18	=F18*F6
G19	G19	=F6*F19
F22	F22	=C5-C37
F24	F24	=C9-C20
F25	F25	=F24+C18+C21+C22+C24+C25+C26+C30
F26	F26	=F22/C5
C20	C20	=B9*B20
C21	C21	=(C20+C18)*B21/12*C6
C22	C22	=B22*B9
C23	C23	=((F25*B23)/12)*C6
C24	C24	=B24/12*C6
C25	C25	=B25*C6
C26	C26	=B26*C6
C27	C27	=SUM(C21:C26)
C31	C31	=C5*B31
C33	C33	=C5*B33
C34	C34	=B34*C5
C35	C35	=SUM(C30:C34)
C37	C37	=C12+C18+C27+C35

 

[igold]

I don't know if this is what you are looking for but if you know your cost and want to figure out a selling price by changing your profit margin then in your example the formula would be:

ARV = Cost / (1-Profit Margin)

Book1
	A	B	C	D	E	F	G	H
1	PROFIT ANALYSIS SHEET
2
3	Property Address
4
5	Estimated Sales Price (ARV)		500000		Property Details
6	Estimated Hold Time (months)		5		Property Square Footage	1441
7	Category	Costs	Total		Number of Bedrooms	3
8	BUYING				Number of Bathrooms	2
9	Offer to Seller	277000	277000		Added Square Footage	0
10	Wholesale Fee	15000	15000		MAXIMUM ALLOWABLE OFFER		277000
11	Closing Costs / Title Ins.	0.02	5840
12	TOTAL ACQUISITION COST		297840
13
14	RENOVATION				Repair Estimate Key
15	Interior Repairs*	57640	66286		Type	Cost per Sq. Ft.	Rehab Cost
16	Exterior Repairs*	5000	5750		Lipstick	20	28820
17	Monthly Maintenance (Pool, Landscape, HOA, etc.)	50	250		Basic	40	57640
18	TOTAL RENOVATION COSTS		72286		Gut Job	60	86460
19					Destroyed	75	108075
20	HOLDING - Loan Amt (LTC)	0.8	221600		Addition	125	0
21	Payment Required (Hard Money %)	0.11	13469.775
22	Loan Arrangement and/or Points	0.01	2770		TOTAL ESTIMATED PROFIT	75359.225
23	Private funds Interest		0
24	Property Taxes (annual)	1500	625		Down Payment	55400
25	Insurance (monthly)	200	1000		Cash Requirements	147200.78
26	Utilities (monthly)	300	1500		Profit Margin	15.07%
27	TOTAL HOLDING COSTS		19364.775
28					Desired Profit Margin	15.07%
29	SELLING				ARV @ Desired Profit Margin	500000
30	Staging/Marketing/Photos/Etc.	150	150
31	Closing Costs, Concessions, BINSR	0.02	10000
32	Seller Credit To Buyer	0	0
33	Sales Commission - Listing Agent	0.025	12500
34	Sales Commission - Buyers Agent	0.025	12500
35	TOTAL SELLING COSTS		35150
36
37	TOTAL PURCHASE, RENOVATION, HOLDING & SALES COSTS		424640.775
38
39	*Total includes 15% buffer
40
Sheet1

Cell Formulas
Range	Formula
G10	G10	=C9
C9:C10,C30,C32	C9	=B9
C11	C11	=(C9+C10)*B11
C12	C12	=SUM(C9:C11)
B15	B15	=G17
C15:C16	C15	=(B15)*1.15
C17	C17	=B17*C6
C18	C18	=C15+C16+C17
G16	G16	=F16*F6
G17,G20	G17	=F17*F6
G18	G18	=F18*F6
G19	G19	=F6*F19
F22	F22	=C5-C37
F24	F24	=C9-C20
F25	F25	=F24+C18+C21+C22+C24+C25+C26+C30
F26	F26	=F22/C5
C20	C20	=B9*B20
C21	C21	=(C20+C18)*B21/12*C6
C22	C22	=B22*B9
C23	C23	=((F25*B23)/12)*C6
C24	C24	=B24/12*C6
C25	C25	=B25*C6
C26	C26	=B26*C6
C27	C27	=SUM(C21:C26)
F29	F29	=C37/(1-F28)
C31	C31	=C5*B31
C33	C33	=C5*B33
C34	C34	=B34*C5
C35	C35	=SUM(C30:C34)
C37	C37	=C12+C18+C27+C35