• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What if Analysis

Y

yvcamasia407

New Member
Hello,
I've tried without success using What If Analysis in Excel. Wondering if someone in this blog can suggest which Excel feature to use....please help me. Here's an example:

Loan Amount: ???
Interest: 1%
Period: 21 months
Profit: 640,000

The 1% interest will be considered as part of the $640,000 Revenue.

Thank you.

Becky
 
Hi Becky ,

You need to say whether the interest is Simple interest or Compound interest ; if it is the latter , what is the frequency of compounding i.e. is it compounded daily , weekly , monthly ?

Narayan
 
Thank you Narayan. So if the the Loan amount let's say $800,000 the Interest amount is $8,000 a month for the next 21 months or a Total of $168,000.
 
Hi, yvcamasia407!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, if I'm not missing anything you neither need to use Solver nor do a what-if analysis; assuming your data is like this:
A1: Loan
B1: x?
A2: Interest
B2: 1%
A3: Period
B3= 21
A4: Profit
B4: 640000

In B1 enter this:
A2: =B4/B3/B2

Regards!

EDITED
 
Last edited:
SirJB7,
Thank you. I have been a member here for over 5 years now I think. It's just that I forgot my username and password. I have even taken some Excel classes from Chandoo.

Regarding this request for help, yes I used equation in 1 unknown. My mistake, I forgot to add that 50% of the Loan amount will be refunded to the borrower. The 1% interest for 21 months will be a part of the other 50%.

I m willing to pay to help me create this template.

Thank you.

Becky
 
Hi, yvcamasia407!

With your registered email you can set a new password, just click on "Forgot your password?". If you don't succeed don't post your email here, send it by a conversation to an administrator (Hui, r1c1).

About your new spec, please post an example indicating your input data, the output required and how did you manage to get it.

Regards!

PS: This username is of middle 2012, a year and a half.
 
Hi Becky ,

Since you want the entire procedure explained , let us start with the basics.

In Simple interest calculations , there are 4 numbers involved viz. Principal (P) , Interest Rate (R) , Interest Period (N) and Interest Amount (I). The Interest Rate is in percentages , while the Interest Period is in months if the Interest Rate is the rate per month , and in years if the Interest Rate is the annual rate i.e. the rate per year.

Out of these 4 numbers , if any 3 are known , the 4th can be calculated.

It is only if less than 3 are known that we need to resort to What If analysis.

Thus , if we know P , N and R , I is calculated using the mathematical equation I = P * N * R / 100 , where the Interest Rate (R) is not a percentage number as Excel knows it ( in Excel , when you enter a value such as 10% , what is actually put in the cell or the formula is 0.10 i.e. 10/100 ) , but the number itself. To work out an example , if we invest 15000 ( in any currency ) , at a monthly rate of 6 % , after 24 months , the accumulated interest would be : 15000 * 6 * 24 /100 = 21,600.

As mentioned earlier , if now , we know the values of I , N and R , we can back-calculate P , using the mathematical equation P = 100 * I / ( N * R ). As an example , if we have earned an interest of 6000 , after investing an unknown amount at an annual rate of 8 % , for 5 years , then the principal amount i.e. the amount I would have had to invest , would have been P = 100 * 6000 / ( 8 * 5 ) = 15000.

Now , to your last twist in the tale viz.
50% of the Loan amount will be refunded to the borrower. The 1% interest for 21 months will be a part of the other 50%.
Click to expand...
If I have understood this , you mean to say that the interest amount is only on half the principal amount ; we can put it differently , that the actual interest is twice the amount actually mentioned , or for the same interest amount , the principal would be twice the calculated figure. Correct me if I am wrong.

Going by this , P = 2 * 100 * I / ( N * R ). Given your values of I = 640000 , N = 1% monthly , R = 21 months , P would come out to be 2 * 100 * 640000 / ( 1 * 21 ) = 6,095,238.

If you wish to verify these calculations , since the interest is 640000 over 21 months , the monthly interest amount is 640000/21 = 30476.19

Since this is 1 % of the principal , the principal is 100 times this amount i.e. 3,047,619 ; since you say that 50 % of the principal is earning interest ( after 50 % has been given back ) , the actual principal is twice this amount i.e. 2 * 3047619 = 6,095,238.

Narayan
 
Sorry SirJB7

At least I blamed someone else for the error
I did respond from my iPhone at 5am so have some sort of lame excuse
 
@Hui
Hi, Master Po!
If it neither was your wife not the police trying to find out your location, then it's nothing to worry about. :p
Regards!
 
Thank you Hui-
After the meeting last night, I am looking to create a dynamic template that our non-financial managers can just input the given to generate the answer of the UNKNOWN (Loan Amount). Profit requirement will vary according to product.


Code: 
Refund %                              50%            75%                                

Loan Amount                         896,479    1,383,696
% refund                            448,239    1,037,772
Difference                          448,239      345,924
Interest for 21 month @1%           188,261      290,576
Non refundable fee                    7,500        7,500
Profit                           $  644,000  $   644,000
 
You must log in or register to reply here.
Back
Top