How to see if my loan payment is within budget with true or false statement

[moniqua33]

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Rabies cost/shot	$ 25.00
2	immunization cost/shot	$ 18.00			Adoption Rates
3	food and body weight	80%			Age (mos)	Amount
4	cost food/pound dog	$ 0.86			5	$ 200.00
5	shot treat cost	$ 2.00			11	$ 160.00
6					12	$ 65.00
7
8
9	Adopted Dogs								Expense Calculations						Revenue
10	Name	Age (in months)	Weight (in pounds)	Sex	City	Rabies shot?	Number of Immunizations	CustomerID	Rabies shot cost	Immunization costs	Total cost shots	Food cost	Treat cost	Total costs	Adoption Cost	Profit
11	Ace	42	6	M	Columbus	TRUE	1	24	$ 25.00	$ 18.00	$ 43.00	$ 40.25	$ 2.00	$ 85.25	$ 65.00	$ 20.25
12	Yoda	162	13	M	Columbus	TRUE	3	28	$ 25.00	$ 54.00	$ 79.00	$ 148.26	$ 6.00	$ 233.26	$ 65.00	$ 168.26
13	Toby	171	16	M	Dublin	FALSE	1	23	$ -	$ 18.00	$ 18.00	$ 158.07	$ 2.00	$ 178.07	$ 65.00	$ 113.07
14	Fluffy	10	4	F	Columbus	TRUE	1	21	$ 25.00	$ 18.00	$ 43.00	$ 11.35	$ 2.00	$ 56.35	$ 160.00	$ (103.65)
15	Daisy	5	2	F	Worthington	TRUE	2	11	$ 25.00	$ 36.00	$ 61.00	$ 5.68	$ 4.00	$ 70.68	$ 200.00	$ (129.32)
16	Corky	3	3	F	Dublin	FALSE	1	43	$ -	$ 18.00	$ 18.00	$ 4.64	$ 2.00	$ 24.64	$ 200.00	$ (175.36)
17	Scruffy	14	5	F	Worthington	FALSE	2	45	$ -	$ 36.00	$ 36.00	$ 15.48	$ 4.00	$ 55.48	$ 65.00	$ (9.52)
18	Lady	10	3	F	Worthington	FALSE	2	2	$ -	$ 36.00	$ 36.00	$ 10.66	$ 4.00	$ 50.66	$ 160.00	$ (109.34)
19	Bailey	174	7	F	Worthington	FALSE	2	42	$ -	$ 36.00	$ 36.00	$ 154.46	$ 4.00	$ 194.46	$ 65.00	$ 129.46
20	Marley	3	2	F	Columbus	FALSE	1	26	$ -	$ 18.00	$ 18.00	$ 3.96	$ 2.00	$ 23.96	$ 200.00	$ (176.04)
21	Bear	66	16	M	Columbus	FALSE	1	22	$ -	$ 18.00	$ 18.00	$ 67.77	$ 2.00	$ 87.77	$ 65.00	$ 22.77
22	Bruno	7	5	M	Columbus	FALSE	3	13	$ -	$ 54.00	$ 54.00	$ 9.46	$ 6.00	$ 69.46	$ 160.00	$ (90.54)
23	Alex	42	17	F	Columbus	FALSE	3	47	$ -	$ 54.00	$ 54.00	$ 47.82	$ 6.00	$ 107.82	$ 65.00	$ 42.82
24	Copper	11	5	M	Columbus	FALSE	3	48	$ -	$ 54.00	$ 54.00	$ 12.90	$ 6.00	$ 72.90	$ 160.00	$ (87.10)
25	Lucky	78	6	F	Columbus	FALSE	1	14	$ -	$ 18.00	$ 18.00	$ 71.21	$ 2.00	$ 91.21	$ 65.00	$ 26.21
26	Alfie	2	1	F	Dublin	TRUE	0	3	$ 25.00	$ -	$ 25.00	$ 2.41	$ -	$ 27.41	$ 200.00	$ (172.59)
27	Rocky	6	3	M	Columbus	FALSE	2	10	$ -	$ 36.00	$ 36.00	$ 7.22	$ 4.00	$ 47.22	$ 160.00	$ (112.78)
28	Allegra	8	3	M	Hilliard	FALSE	0	30	$ -	$ -	$ -	$ 8.94	$ -	$ 8.94	$ 160.00	$ (151.06)
29	Chance	2	3	M	Columbus	TRUE	0	31	$ 25.00	$ -	$ 25.00	$ 3.78	$ -	$ 28.78	$ 200.00	$ (171.22)
30	Milo	88	8	M	Hilliard	FALSE	3	32	$ -	$ 54.00	$ 54.00	$ 81.18	$ 6.00	$ 141.18	$ 65.00	$ 76.18
31	Alvin	28	15	M	Columbus	TRUE	1	33	$ 25.00	$ 18.00	$ 43.00	$ 34.40	$ 2.00	$ 79.40	$ 65.00	$ 14.40
32	Amadaous	6	3	M	Hilliard	TRUE	0	34	$ 25.00	$ -	$ 25.00	$ 7.22	$ -	$ 32.22	$ 160.00	$ (127.78)
33	Amber	12	5	F	Columbus	TRUE	0	35	$ 25.00	$ -	$ 25.00	$ 13.76	$ -	$ 38.76	$ 65.00	$ (26.24)
34	Teddy	8	5	M	Columbus	TRUE	2	36	$ 25.00	$ 36.00	$ 61.00	$ 10.32	$ 4.00	$ 75.32	$ 160.00	$ (84.68)
35	Ameila	173	11	F	Columbus	TRUE	0	37	$ 25.00	$ -	$ 25.00	$ 156.35	$ -	$ 181.35	$ 65.00	$ 116.35
36	Champ	30	5	M	Columbus	FALSE	3	38	$ -	$ 54.00	$ 54.00	$ 29.24	$ 6.00	$ 89.24	$ 65.00	$ 24.24
37	Amy	11	3	F	Columbus	TRUE	3	39	$ 25.00	$ 54.00	$ 79.00	$ 11.52	$ 6.00	$ 96.52	$ 160.00	$ (63.48)
38	Molly	10	4	F	Columbus	TRUE	3	40	$ 25.00	$ 54.00	$ 79.00	$ 11.35	$ 6.00	$ 96.35	$ 160.00	$ (63.65)
39	Prince	2	1	M	Gahanna	FALSE	0	41	$ -	$ -	$ -	$ 2.41	$ -	$ 2.41	$ 200.00	$ (197.59)
40	Bella	66	6	F	Columbus	TRUE	3	25	$ 25.00	$ 54.00	$ 79.00	$ 60.89	$ 6.00	$ 145.89	$ 65.00	$ 80.89
41	Andy	12	13	M	Columbus	FALSE	2	44	$ -	$ 36.00	$ 36.00	$ 19.26	$ 4.00	$ 59.26	$ 65.00	$ (5.74)
42	Angelica	4	2	F	Worthington	TRUE	1	6	$ 25.00	$ 18.00	$ 43.00	$ 4.82	$ 2.00	$ 49.82	$ 200.00	$ (150.18)
43	Lola	112	12	F	Columbus	FALSE	3	7	$ -	$ 54.00	$ 54.00	$ 104.58	$ 6.00	$ 164.58	$ 65.00	$ 99.58
44	Charlie	41	15	M	Columbus	TRUE	2	9	$ 25.00	$ 36.00	$ 61.00	$ 45.58	$ 4.00	$ 110.58	$ 65.00	$ 45.58
45	Laddie	4	3	M	Dublin	TRUE	1	48	$ 25.00	$ 18.00	$ 43.00	$ 5.50	$ 2.00	$ 50.50	$ 200.00	$ (149.50)
46	Apollo	1	1	M	Columbus	TRUE	0	17	$ 25.00	$ -	$ 25.00	$ 1.55	$ -	$ 26.55	$ 200.00	$ (173.45)
47	Lanie	4	3	F	Columbus	FALSE	0	5	$ -	$ -	$ -	$ 5.50	$ -	$ 5.50	$ 200.00	$ (194.50)
48	Sport	90	15	M	Worthington	TRUE	0	16	$ 25.00	$ -	$ 25.00	$ 87.72	$ -	$ 112.72	$ 65.00	$ 47.72
49	Jamie	1	1	M	Hilliard	FALSE	0	18	$ -	$ -	$ -	$ 1.55	$ -	$ 1.55	$ 200.00	$ (198.45)
50	Rio	9	4	M	Columbus	TRUE	3	37	$ 25.00	$ 54.00	$ 79.00	$ 10.49	$ 6.00	$ 95.49	$ 160.00	$ (64.51)
51	Beau	10	4	F	Hilliard	FALSE	0	17	$ -	$ -	$ -	$ 11.35	$ -	$ 11.35	$ 160.00	$ (148.65)
52	Coco	1	2	F	Worthington	FALSE	0	19	$ -	$ -	$ -	$ 2.24	$ -	$ 2.24	$ 200.00	$ (197.76)
53	Scout	11	4	M	Columbus	TRUE	3	29	$ 25.00	$ 54.00	$ 79.00	$ 12.21	$ 6.00	$ 97.21	$ 160.00	$ (62.79)
54	Sugar	3	2	F	Columbus	TRUE	0	15	$ 25.00	$ -	$ 25.00	$ 3.96	$ -	$ 28.96	$ 200.00	$ (171.04)
55	Ariel	1	2	F	Dublin	FALSE	0	4	$ -	$ -	$ -	$ 2.24	$ -	$ 2.24	$ 200.00	$ (197.76)
56	Zoey	7	3	M	Dublin	TRUE	2	41	$ 25.00	$ 36.00	$ 61.00	$ 8.08	$ 4.00	$ 73.08	$ 160.00	$ (86.92)
57	Rusty	4	1	M	Columbus	TRUE	0	5	$ 25.00	$ -	$ 25.00	$ 4.13	$ -	$ 29.13	$ 200.00	$ (170.87)
58	Snow	8	4	M	Columbus	FALSE	3	12	$ -	$ 54.00	$ 54.00	$ 9.63	$ 6.00	$ 69.63	$ 160.00	$ (90.37)
59	Indy	12	8	F	Dublin	TRUE	3	32	$ 25.00	$ 54.00	$ 79.00	$ 15.82	$ 6.00	$ 100.82	$ 65.00	$ 35.82
60	Shadow	8	4	M	Columbus	TRUE	3	8	$ 25.00	$ 54.00	$ 79.00	$ 9.63	$ 6.00	$ 94.63	$ 160.00	$ (65.37)
61
62	Number of dogs needing Rabies shots	26													TOTAL	$ (3,116.19)	monthly
63
64	City	Average Profit														$ (37,394.26)	yearly
65	Columbus	$ (43.89)
66	Dublin	$ (90.46)
67	Gahanna	$ (197.59)
68	Hilliard	$ (109.95)
69	Worthington	$ (59.85)
70	TOTALS	$ (501.74)
Adopted Dogs

Cell Formulas
Range	Formula
I11:I60	I11	=IF(F11,$B$1,0)
J11:J60	J11	=PRODUCT(G11,$B$2)
K11:K60	K11	=SUM(I11,J11)
L11:L60	L11	=SUM(C11*$B$3,B11)*$B$4
M11:M60	M11	=PRODUCT(G11,$B$5)
N11:N60	N11	=SUM(K11,L11,M11)
O11:O60	O11	=IF(B11<=$E$4,$F$4,IF(B11<=$E$5,$F$5,IF(B11>=$E$6,$F$6)))
P11:P60	P11	=N11-O11
B62	B62	=COUNTIF(F11:F60,TRUE)
P62	P62	=SUM(P11:P60)
P64	P64	=AVERAGE(P62*12)
B65:B69	B65	=AVERAGEIFS(P11:P60,E11:E60, A65)
B70	B70	=SUM(B65:B69)

Book3
	A	B	C	D	E	F	G	H	I	J
1	Options	Present Value	Future Value	Annual Interest Rate	# Compounding periods	Type	Years	Payment	RESULT (TRUE or FALSE)	Explanation
2	1	$ 95,000.00		3.5%	12		5	($1,728.22)	FALSE
3	2
Sheet1

Cell Formulas
Range	Formula
H2	H2	=PMT(D2/E2,G2*E2,B2)
I2	I2	=IF(H2<='[PerkyPupDogShelterAML.xlsm]Adopted Dogs'!P62,TRUE,FALSE)

I am trying to find out if my monthly loan payment is in my monthly budget with a true or false statement. Please help

 

[6StringJazzer]

I don't see any figure on here that is a budget. How do you determine your monthly budget? I just see costs/profit.

 

[moniqua33]

I don't see any figure on here that is a budget. How do you determine your monthly budget? I just see costs/profit.

Hi there, I may have misspoke. I need to know if my loan payment Book3!H2 is within my monthly profit in AdoptedDogs!P62.

 

[BSALV]

imho and off topic, column L,

food cost = .86 * age (in months) + .69 * weight (in pounds)

that can't be correct.
a dog between 1 and 5 years, food cost is constant and 100% weight related ??? !!! ???
Earlier and later, food is perhaps more expensive, but that 'll never increase at a rate of 10$/month per year of life.

Are you sure of your data before you calculate the rest ?

At the end of the month, you lost 3.116$ and you have to pay an extra 1.728$ for your loan.
Big problem ?

 

[moniqua33]

imho and off topic, column L,

food cost = .86 * age (in months) + .69 * weight (in pounds)

that can't be correct.
a dog between 1 and 5 years, food cost is constant and 100% weight related ??? !!! ???
Earlier and later, food is perhaps more expensive, but that 'll never increase at a rate of 10$/month per year of life.

Are you sure of your data before you calculate the rest ?

At the end of the month, you lost 3.116$ and you have to pay an extra 1.728$ for your loan.
Big problem ?

This is a hypothetical problem and only calculates one month of expenses, not future months.

In Book3! The PV is if I were to take a $95,000 loan out, what would my monthly payment be. Then I needed to find if my monthly payment of this loan, is within my monthly profit for the given month. Does that make sense?

 

[BSALV]

That 1,728$ is okay, is correct.
The question, if you can pay that loan, i don't know, you only mentioned food and vet, but don't you have other costs (electricity, heating, water, building, ...)

 

[6StringJazzer]

In column P you are getting profit by costs - revenue but it should be revenue - costs.

Your monthly loan amount should be

=-PMT(D2/E2,G2*E2,B2)

to get a positive loan amount.

Then your formula in I2 would be

=H2<='[PerkyPupDogShelterAML.xlsm]Adopted Dogs'!P62

It will be TRUE when the profit exceeds the loan amount (i.e., TRUE when you can afford the payment), otherwise FALSE.