EEGR400 Morgan State Projection of Monthly Expenses Finance Assignment Assume you are employed at $60,000 per year. Consider such deductions as social secu

EEGR400 Morgan State Projection of Monthly Expenses Finance Assignment Assume you are employed at $60,000 per year. Consider such deductions as social security, income taxes (federal, state and county) and your payment on health benefits. Do the best you can to come to grips with what is a realistic monthly take home pay.Develop a projection of monthly expenses; food, clothing, housing, auto payment, auto insurance, gasoline, utilities, etc. (refer to the list of possible expenses outlined in the Money Management PowerPoint presentation). Use Excel to organize your list of projections. MORGAN STATE UNIVERSITY
DEPARTMENT OF ELECTRICAL AND COMPUTER ENGINEERING
EEGR 400 INTRODUCTION TO PROFESSIONAL PRACTICE
FINANCE ASSIGNMENT
**THIS ASSIGNMENT REQUIRES THE USE OF MICROSOFT EXCEL**
Refer to information page on the use of some finance features in Excel. Understand
financial functions @PMT, @PPMT, @IPMT, @PV and @FV. See here.
1. Assume you are employed at $60,000 per year. Consider such deductions as
social security, income taxes (federal, state and county) and your payment on
health benefits. Do the best you can to come to grips with what is a realistic
monthly take home pay.
2. Develop a projection of monthly expenses; food, clothing, housing, auto payment,
auto insurance, gasoline, utilities, etc. (refer to the list of possible expenses
outlined in the Money Management PowerPoint presentation). Use Excel to
organize your list of projections.
3. Use Excel to determine the above monthly auto payment and possible housing
payment. Let’s try a scenario that will help you to figure out what payment is best
for you.
A. First find the monthly payment on a $10,000 automobile. Assume 6.5% over
36 months with no money down.
B. Similarly, determine the same for a $30,000 automobile.
C. Assume, as an alternative, you decide to wait for a year, and that you place
whatever monthly payment you would be paying on either car into an interest
bearing savings account, or small certificates of deposit paying 2.0% per
month. Use the @FV function to determine this value.
D. Now, patting yourself on the back for your self-restraint over the year, you
take this amount and use it as a down payment to buy either car (except it is a
year later). Assume the same terms: 36 months with 6.5% interest. Again,
calculate the new monthly payment and compare with A and B. How much
extra “luxury income” do you have per month as a benefit of your selfrestraint.
E. Surrounding Morgan State University, $750/month buys an adequate
apartment. I assume, even with this $750, you could still swing the $30,000
automobile in B. However, if you go for option A and take the savings
between B and A, you have that much more (than the $750 you would have to
pay for an apartment) that you could use to purchase a modest home. Use
Excel to determine how much house you could buy with $750 + (Payment in
B- Payment in A). Assume 6.0% over 360 months.
F. Assume a loan of $30,000, 36 months, 6.5% interest. Generate a table of
payments showing the principal payment and the interest payment for each of
the 36 months.
4. After all monthly expenses are accounted for, place 5% of the balance in your
savings account. How much money will you have left from your take home pay?
5. Be sure to list your projected monthly expenses clearly on your Excel
spreadsheet. Include your chosen monthly automobile and housing payment
calculated in the above steps as well as your savings amount. You must clearly
highlight your choice of car (purchased with/without a down payment) and a
modest home or apartment.
References
= pv(rate, nper, pmt)
Please see PV Function Help
= pmt(rate, nper, pv)
Please see PMT Function Help
= ppmt(rate, per, nper, pv)
Please see PPMT Function Help
= ipmt(rate, per, nper, pv)
Please see IPMT Function Help
=fv(rate, nper, pmt, [pv])
Please see FV Function Help
Finance Using Excel – Loans
This tutorial illustrates the use of functions @pmt, @ppmt and @ipmt. These may among
the most important things you will learn while at Morgan.
Introduction
Assume you borrow $10,000 over a period of 36 months at 7.0 percent interest. An
inexperienced person may fall for the following argument;
7 percent per year * 3 years * $10,000 = $2100 interest
Thus, monthly payment
= $10,000/36 + $2100/36
= $ 277.77 + $58.33 = $336.10
Indeed, lenders used to represent this as 7.0 percent interest. However logical it may
seem, it is not. You should indeed pay a total of $2100 in interest if you held the full
$10,000 for the full three years. But in making monthly payments on the principal, the
principal is declining. You do not have the advantage of the the full $10,000 for the 36
months.
In fact, with your first payment, you are paying $58.33 for having $10,000 for but a
single month which is indeed seven percent. However, on the last month, you have the
benefit of only $277.77 for that one month. In paying $58.33 for that single month, you
are paying $58.33 / $277.77 * 12 = 250 percent interest.
The point is, what seems logical, often is not and it can be costly.
This concept might be clearer with a simpler example. Assume you borrow $1000 for one
year at 7.0 percent interest. The lender calculates the interest as $1000 * 0.07 = $70 and
asks for two equal payments; the first after six months, the second after the full year. This
is not 7.0 percent interest, as you really only had the use of an average of $750 for a year.
In paying $70, your effective interest rate was closer to $70 / $750 = 9.33 percent.
When taking a loan, you should be only paying on the remaining balance. Payments in
the beginning will contain more dollars in interest than later payments.
Thus, calculating constant payments is somewhat complex. What constant payment
PAYMENT, consisting of PRINCIPAL_i and INTEREST_i over n periods results in a
sum of PAYMENT_i which equals the amount of the loan, while the INTEREST_i is
only the interest on the outstanding balance for that period. Of course, mathematicians
have developed expressions to do this, but lay people have been left to use tables, page
after page of tables for varying principal, interest and time.
Loan Functions using Excel.
However, you can do more by knowing three simple Excel functions.
@pmt(rate, nper, pv)
@ppmt(rate, per, nper, pv)
@ipmt(rate, per, nper, pv)
where;
rate is interest rate per period; e.g., 0.07/12.
nper is the number of equal payments; e.g., 36.
pv (present value) is the amount that is borrowed; e.g., $10,000.
per is the period under consideration; e.g., period 24.
In our example of $10,000 over 36 months at 7.0 percent interest;
Monthly payment is calculated;
@pmt(0.07/12, 36, 10000) equals $308.77
That portion of payment 12 which is principal;
@ppmt(0.07/12, 12, 36, 10000) equals $266.98.
That portion of payment 12 which is interest;
@ipmt(0.07/12, 12, 36, 10000) equals $41.79.
Note that for any period,
@ppmt + @ipmt = @pmt
The arguments may be cells, which may then easily be modified to examine various
options; e.g.,
@pmt($b$1, $b$2, $b$3)
Tables
Assume you desire a table, indicating month by month the principal and interest portions
of the payment.
A
B
1
2
3
4
Interest =
N periods =
Loan Amt =
Payment =
9
10
11
12
Period
1
+a10+1
C
0.07/12
36
10000
@pmt(b1, b2, b3)
Principal
@ppmt($b$1, a10, $b$2, $b$3)
Interest
@ipmt($b$1, a10 $b$2, $b$3)
Cell a11 may then be copied to a12..a45 so as to generate periods 1..35. Cells b10..c10
may be copied to b11..c45. You then have a table of payments, including the amount of
principal and interest paid each month
You may also use the @sum function to calculate the total amount of principal (or
interest) paid as a function of time.

Purchase answer to see full
attachment