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]

[TABLE="width: 537"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Revenue[/TD]
[TD]Growth[/TD]
[TD]Expenses[/TD]
[TD]Growth[/TD]
[TD]Loan Repayment[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] $ 50.000,00[/TD]
[TD]8%[/TD]
[TD] $ 30.000,00[/TD]
[TD]4%[/TD]
[TD] $ 25.000,00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]per year[/TD]
[TD]per year[/TD]
[TD]per year[/TD]
[TD]per year[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD] Revenue[/TD]
[TD]Expenses[/TD]
[TD]Loan Repayment[/TD]
[TD]Profit/Loss[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD] $ 50.000,00[/TD]
[TD] $ 30.000,00[/TD]
[TD] $ 25.000,00[/TD]
[TD] $ -5.000,00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD] $ 54.000,00[/TD]
[TD] $ 31.200,00[/TD]
[TD] $ 25.000,00[/TD]
[TD] $ -2.200,00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD] $ 58.320,00[/TD]
[TD] $ 32.448,00[/TD]
[TD] $ 25.000,00[/TD]
[TD] $ 872,00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4 [/TD]
[TD] $ 62.985,60[/TD]
[TD] $ 33.745,92[/TD]
[TD] $ 25.000,00[/TD]
[TD] $ 4.239,68[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5 [/TD]
[TD] $ 68.024,45[/TD]
[TD] $ 35.095,76[/TD]
[TD] $ 25.000,00[/TD]
[TD] $ 7.928,69[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6 [/TD]
[TD] $ 73.466,40[/TD]
[TD] $ 36.499,59[/TD]
[TD] $ - [/TD]
[TD] $ 36.966,82[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7 [/TD]
[TD] $ 79.343,72[/TD]
[TD] $ 37.959,57[/TD]
[TD] $ - [/TD]
[TD] $ 41.384,15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8 [/TD]
[TD] $ 85.691,21[/TD]
[TD] $ 39.477,95[/TD]
[TD] $ - [/TD]
[TD] $ 46.213,26[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.