Thursday, November 8, 2018

Loans Analyzed with PMT and PV




Question One:   A person has $25,000 to lend for 20 years.

What is the monthly payment received if the annual interest rate is 3 percent per year?  

What is the monthly payment received if the annual interest rate is 6 percent per year?

Answer:  This problem is solved below with the PMT function.

Impact of Interest Rates on Bond Payments
rate
nper
PV
PMT
0.03
240
25000
$138.65
0.06
240
25000
$179.11
Diff.
$40.46


Question Two: How much should a person loan to receive a $300 monthly payment on a loan when the annual interest rate is 3 percent per year?

How much should a person invest to receive a $600 monthly interest payment on a bond at the same 3 percent annual interest rate?

Answer:   The required loan is calculated with the PV function.

Required Bond Purchase
Rate
nper
PMT
PV
0.03
240
300
$54,093.27
0.03
240
600
$108,186.55

Note that when the interest rate is doubled and the term (nper) is unchanged the required bond purchase doubles.

Note also that the two problems presented here involve loans not bonds.   The output from the PMT function is both interest and repayment of principal.


More questions solving finance problems in Excel can be found here.






Friday, October 19, 2018

Simulations of Senate Control with RAND and IF Statements in Excel




Question:  The table below has an analyst’s assumptions on the probability of the Democrat winning in 9 Senate races that are currently in contention.   Assume all races are independent.   Currently the Democrats have 5 seats and need to gain 2 seats to get control of the Senate.

State
Prob Dem Win
Dum if State is currently held by Democrat
AZ
0.55
0
FL
0.55
1
IN
0.55
1
MO
0.55
1
NV
0.55
0
MT
0.6
1
ND
0.5
1
TN
0.4
0
TX
0.4
0


Based on these probabilities and the assumption of independent outcomes for state races use the RAND and IF statement to generate the frequency of potential senate outcomes after the November 2018 election.

Discuss how the spreadsheet is created.

Discuss how the assumption of independent state outcomes impacts results.

Creation of Senate Outcome Spreadsheet:

Follow these steps:

Use Rand() statement to create at least 2000 random outcome numbers  between 0 and 1 for each state.   Columns E through M starting at row 3 have the random outcomes for the nine states.

Row 2 of Columns 2 through M have the probability of the Democrat candidate winning the election.

Use if statement to create a dummy outcome variable 1 if Dem wins 0 if Rep wins for all random outcomes for all states.  Outcome variables are in Row O to Row W.

Remember row 2 has the probability of Democratic Candidate winning.

The syntax for first observation on outcome AZ first observation in cell O3 is


If(E3<=E$2,1,0)

Copy O3 to P3:W3.

Copy O3:W3 down at least 2000 cells.

In column X sum columns O to W.

Final step is take frequency of totals for the 2000 plus observations Use the FREQUENCY function.


Total Democratic Wins
# Observations
0
3
1
11
2
84
3
310
4
514
5
602
6
382
7
135
8
20
9
0
Sum
2061
7, 8  or 9
0.075206211
6,7,8 9
0.26055313


To take Control of the Senate Democrats need to win 7 of these races because Pence the Veep breaks the tie,

The likelihood of this happening with these optimistic probability assumptions is around 7.5%.

Under these assumptions, the probability of a 50/50 Senate with the tie going to the Republican is 26%.

The most likely outcome. based on these probabilities and the assumption of independent state outcomes, is Democrats take 5 seats and Senate remains at 51-49 Republican:

The role of independent state outcomes:

The results presented here assume state outcomes are independent.  State outcomes can be influenced by national or world events.   The Kavenaugh hearings appeared to help Republicans in several states.   A surprise event like continual declines in the stock market or perhaps solid proof the Putin has been controlling Trump might help Democrats in more than one states.

Also, the big unknown is who will go to the polls.  Pollsters rely on the concept of the likely voter.  However, new people are registering, both Democrats and Republicans, and we don’t know who will go to the polls.   The difference between likely and actual voters may not be independent across states.