Solved

Excel Financial Scenario

Posted on 2001-08-14
8
388 Views
Last Modified: 2008-07-03
Is there a way of having excel compare 2 financial scenarios. Compound interest  ie Future value of "x" dollars after say 10 years. Versus a receipt of payment of say "y" dollars every month for 10 years with interest on this increasing balance. Which investment would be better and where the break even would be.
Jerome Klein
0
Comment
Question by:JRKLEIN
  • 4
  • 3
8 Comments
 
LVL 6

Expert Comment

by:xSinbad
ID: 6386962
I have made up something for you. You can get it from
http://www.webizz.com.au/help/Compound.xls

Is this what you need?
0
 

Author Comment

by:JRKLEIN
ID: 6387022
It has the first part of what I need.
2nd part is i receive a payment of 500 dollars every month and invest it at say 3% after say 10 years what does this amount to??

A tenant wants me to finance his $10,000  repairs by increasing his rent say $500 a month.
I wanted to see where and when the breakeven point would be by either leaving the money in my bank versus giving it to him and getting more rent back per month.
Jerome
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6387033
That makes things a little more complicated.
0
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
LVL 6

Accepted Solution

by:
bkpchs237 earned 300 total points
ID: 6387037
JRKLEIN,

I set up a small worksheet with the following items to accomplish the task:
cells A1 thru A10 have the numbers 1 thru 10;
cell b4 has the title: rate;
cell b5 has the title: nper;
cell b6 has the title: pmt;
cell b7 has the title: pv;
cell b8 has the title: type.

cells c4 thru c8 have the values for your annual comparison, I used (5%,10,1000,7000,0) respectively.

cells d4 thru d8 have the values for the monthly comparison, I used (c4/12,c5*12,25,10000,1) respectively.

Then setup a chart with the titles first thru tenth going down one column, with headings Annually and Monthly in two adjacent columns with the following formulas: Annual first: =FV($C$4,A1,$C$6,$C$7,$C$8)*-1   (entered in cell C12, then copied down column to cell C21); and Monthly first: =FV($C$4/12,A1*12,$D$6,$D$7,$D$8)*-1  (entered in cell D12, then copied down column to cell D21).

This will give you values for each item compounded per the Future Value function at each year's occurrence.  Thus in my example the starting point for Annual would be $7000 at 5% compounded annually, with a payment into the fund of $1000 at the end of each period (1 year) for 10 years resulting in a total of $23,980.15 after 10 years.  Further, by this example the starting point for Monthly would be $10,000 at 5% compounded monthly, with a payment into the fund of $25 each period (1 month) for 10 years resulting in a total of $20,368.33. [Note: I used these figures because they intersect each other.]

Now that you have your totals for these lines (you could plot on a line chart to see a graphic display) you can calculate the intersection (break-even point).

This is done by calculating the slope of each line (already have the intersections as the starting points) and using the annual period as the x-values (from 0 thru 10 in this example) using the two-point form of line equations.  Slope of the Annual values (m1) would be =(C21-C7)/10 [placed in cell C25].  Likewise for Monthly values (m2) would be =(D21-D7)/10  [placed in cell D28].  Then you could calculate the x-intercept by solving two equations together for x using y=m1x+b1 minus y=m2x+b2 by the formula =(D29-C7)/(C25-D7).  This would yield a result in the annual period (years) with a value between 0 and 10, in this case 4.5373233 [placed in cell C31] years from the starting point.  The y-intercept value would be calculated by the following formula =D28*C31+D29 which would yield a result in dollars of the break even point, in this $14704.45.

As far as scenarios to compare the values over time, if you set your spreadsheet up in this manner and use the Scenario Manager (Tools, Scenarios) Add a Scenario and choose your changing cells from the original criteria in pairs (i.e. cells c6 and d6; or c7 and d7; etc.) you would see your results in a flash when you choose show and obtain the results desired.

I know this is a bit complicated and hard to explain in this format.  I have saved a file with all of this already laid out if you desire.  List your email address and I'll gladly send it to you.

Hope this helps.




0
 

Author Comment

by:JRKLEIN
ID: 6387046
My email address is cilc@prodigy.net
Thanks
Jerome
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6387048
I think you should look at the FV function in excel.
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6387053
Sorry about that I did not see bkpchs237's answer until it was too late.
0
 

Author Comment

by:JRKLEIN
ID: 6387131
Dear Sir:
This is wonderful and complex. I guess after seeing the chart the question
I should really be asking is what increase in rent does the Tenant have to pay for me to break even say after 5 years or after 10 years if I give him say
10,000 for upgrades or more if he asks for more.
Can you set up a spread sheet where i can plug in the variables and get the result.
Jerome


0

Featured Post

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Index Match Vlook up for a master file 7 67
Copying and pasting pictures from Excel 2 48
Office 2016 User Guides 5 37
microsoft word short cuts 3 37
This article will show you how to use shortcut menus in the Access run-time environment.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question