EXCEL RAND/PMT FUNCTION!! T___T Hello~ Can somone help me to solve this questions? KINDLY HELP

[yys0501]

HELLO EVERYBODY!!

CAN YOU GUYS HELP ME HOW TO SOLVE THIS QUESTIONS?? REALLY NEED YOUR HELP GUYS COZ I'M NOT GOOD IN EXCEL ESPECIALLY ABOUT RAND AND PMT FUNCTIONS. SO I HOPE YOU CAN HELP ME TO SOLVE THIS QUESTIONS.

..............................................................................................................................................................................

Uncle John has taken a loan from the bank to start a new business. He expects a first year revenue of $50,000 and, thereafter, revenue growing at 8% per year. His non-loan expenses are initially $30,000 per year and this grows a 4% per year. His loan repayment is $25,000 per year for 5 years.

A. construct a spreadsheet model to examine Uncle John business financials
B. Is the business viable within the first 8 years? when does it break even?
C. Do you think the loan amount is sufficient for overcoming cashflow concerns? how do you know?
D. what if the first year revenue is $45,000, growing at 7%; first year expense is $25,000, growing at 3%; and loan repayment is $20,000 per year for 5 years?

...........................................................................................................................................................................

THANKS GUYS I HOPE YOU ARE CAN HELP ME

 

[Jon von der Heyden]

Hi

What exactly is your question? What I see here is some sort of school assignment but I can't work out what part of Excel specifically you need help with. And of course we don't just want to go ahead and do your homework for you - that would be cheating.

If it's function help that you are after then have a look here. This includes help on PMT and RAND.

https://support.office.com/en-gb/ar...category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

Regards
Jon

 

[yys0501]

I dont know how to find the break even for this question. May i know what excel function should i use ?

 

[Jon von der Heyden]

Excel does not have a break-even function. Can you please present an illustration of the business financials (satisfying question A) and subject to this I will help you work out an Excel formula to calculate the break-even.

 

[yys0501]

	Revenue	Growth	Expenses	Growth	Loan Repayment
	$ 50.000,00	8%	$ 30.000,00	4%	$ 25.000,00
		per year	per year	per year	per year
Year	Revenue	Expenses	Loan Repayment	Profit/Loss
1	$ 50.000,00	$ 30.000,00	$ 25.000,00	$ -5.000,00
2	$ 54.000,00	$ 31.200,00	$ 25.000,00	$ -2.200,00
3	$ 58.320,00	$ 32.448,00	$ 25.000,00	$ 872,00
4	$ 62.985,60	$ 33.745,92	$ 25.000,00	$ 4.239,68
5	$ 68.024,45	$ 35.095,76	$ 25.000,00	$ 7.928,69
6	$ 73.466,40	$ 36.499,59	$ -	$ 36.966,82
7	$ 79.343,72	$ 37.959,57	$ -	$ 41.384,15
8	$ 85.691,21	$ 39.477,95	$ -	$ 46.213,26

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Jon von der Heyden]

Cool. So lets assume that the financials are in A1:E9

=IF(COUNTIF(E2:E9,">0"),LOOKUP(2,1/(E2:E9<0),A2:A9)+1,NA())

So if there is no profit in E2:E9 then the result will be #N/A.

Otherwise it will find the last loss in E2:E9 (last -ve figure) and return the adjacent year in A2:A9, and add one because it assumes that the next figure will be profit.