{"id":126480,"date":"2018-02-20T23:50:30","date_gmt":"2018-02-20T23:50:30","guid":{"rendered":"https:\/\/writemyessayfree.com\/itm"},"modified":"2017-08-16T06:10:15","modified_gmt":"2017-08-16T06:10:15","slug":"itm","status":"publish","type":"post","link":"https:\/\/www.benedictsol.com\/blogs\/itm\/","title":{"rendered":"ITM"},"content":{"rendered":"
\n

ITM
Here is some of the criteria:<\/p>\n

And attached is the assignment.<\/p>\n

6 Interest<\/p>\n

1 points paid
360 loan term
not paying up front<\/p>\n

Actual interest rate = B1-(lookup(B2,Sheet2!A3:B9))
B7= IF(B6=0, B8*B2%,0)
B9= B7+B8
B5= PMT(B3%\/12, B4, B9)
B11=B9
C11=B5
D11=B11*$B$3%\/12
E11=C11-D11
F1=B11-E11
B12=F11<\/p>\n

Don\u2019t forget to use $ for absolute cell reference
For the chart:
Layout 10
delete black lines
vertical axis: $ horizontal axis: # of principal months title: Interest Expense vs Principal Reduction<\/p>\n

Interest Rate 6
Points Paid 1
Actual Interest Rate 5.75
Loan Term 360
Monthly Payment $2,947.04
Paying Ponts Up Front? 1=YES 0=NO 0
Cost of Paying Points 5000
Starting Balance 500000
Actual Starting Balance 505000
Monthly Date Actual Starting Balance Monthly Payment Interest Expense Principle Reduction New Balance
1\/1\/2004 505000 $2,947.04 2419.791667 $527.25 $504,472.75<\/p>\n

ITM 3060: Microsoft Excel Project #1 (Part 1)
Home Loan Amortization Table:<\/p>\n

This project is to be completed individually. You can use any computer running a recent version of Excel (Excel 2000, Excel 2002, Excel 2003 or Excel 2007 is
recommended) to complete this project. Excel 2007 is running in most of the computer labs on campus and in all of the College of Business and Economics Computer labs.
(VBT 220).<\/p>\n

Project#2 Part 1 Problem: Let\u2019s assume you go to work for a mortgage broker. Your first project from your boss is to create a loan amortization spreadsheet that can
be used to provide clients\u2019 information about their repayment schedule of their home loan. This amortization schedule should meet the following requirements:<\/p>\n

1. The schedule can be used for loan periods of 15, 20, and 30 years. In other words, 180 months, 240 months, and 360 months can be entered into cell B2 (see
below)
2. Interest rates change daily and by changing the value of the interest rate in cell B1, the entire table must be able to update with the new information.
3. Clients have the option to pay points. Points are basically prepaid interest expressed in percent that can be paid in advance and as a result a lower yearly
interest rate is obtained for the loan. Assume this is the point interest rate reduction schedule:<\/p>\n

Points Paid Yearly interest rate
reduced by this amount
0 0
0.5 .125
1 .25
1.5 .375
2.0 .50
2.5 .625
3 .75<\/p>\n

For example:<\/p>\n

If a client were to obtain a loan of $400,000 and pay 1 point, the extra cost of paying 1 point would be:
$400,000 * 1% = $4,000<\/p>\n

If a client were to obtain a loan of $400,000 and pay 1.5 points, the extra cost of paying 1.5 points would be:
$400,000 * 1.5% = $6,000<\/p>\n

4. Clients have the option to pay the amount of points with a check to the broker or they may \u201croll\u201d the amount of the points into the loan amount. There will be
a cell in the spreadsheet to answer this question (B6)
5. You will use the =lookup function in Microsoft Excel to determine the amount by which to reduce the interest rate. I will demonstrate the use of this function
to you in class.
6. Assume it is currently January of 2004. You will format Cells A9 through A368 to Date Format and use the second choice under type for the date format. I will
demonstrate this in class.
7. Enter in cell A9 the date 1\/1\/2004. The dates will increment by one month. You will \u201cfill\u201d the remaining dates in cells A10 through A368 with the fill
function. I will demonstrate this in class.
8. Your completed table will show the loan as it amortizes over the period specified. After you have completed the table, you will then make a graph that shows
the relationship between the Monthly Payment, Interest Expense, and Principal Reduction over the entire period of the loan and save the graph as a new sheet. I will
demonstrate this in class.<\/p>\n

A B C D E F
1 Interest Rate Enter Interest Rate Here Put Your Name Here
2 Points Paid Enter Points Paid Here
3 Actual Interest Rate Interest Rate minus rate reduction from paying points (use a formula)
4 Loan Term Enter Term in Months Here
5 Monthly Payment Calculated with a formula here (use the =pmt function)
6 Paying points up front? 1=YES, 0=NO Enter 1 (pay points up front) or 0 (rollover points into the loan amount) here if applicable
7 Cost of Paying Points Calculated with a formula multiplying the point amount percent by the loan amount, only to be calculated if the answer to the question
in cell B6 was 0 (NO) (Use an =if statement to do this)
8 Starting Balance Enter Starting Balance of Loan
9 Actual Starting Balance Calculated with a formula
10 Monthly Date Actual Starting Balance Monthly Payment Interest Expense Principal Reduction New Balance
11 Enter Date Here
For example:
1\/1\/2004 Cell reference to B9 Calculate with
a formula: Use =pmt function Calculate with
a formula (monthly interest rate * loan balance Calculate with
a formula (monthly payment minus interest expense) Calculate with
a formula (current balance minus principle reduction)
12 2\/2\/2004 (use fill function) Reference to cell
F11 Reference to cell C11 Reference to cell D11 Reference to Cell E11 Reference to cell F11
Required to be turned in:<\/p>\n

1. Your completed Excel Spreadsheet File, which includes (1) the loan amortization table as well as (2) the loan graph in a separate sheet, i.e. you will have one
sheet called Sheet1 by default and another called Chart1 by default. These two sheets will be in one Excel File. (Please Put your First and Last Name in Cell F1).
Also, save the file with the name of your First_Lastname. For example, my file if I were doing the project would be called:<\/p>\n

Kenneth_Pefkaros.xls<\/p>\n

2. You must complete your Excel Spreadsheet File by the due date given in class. Important: Do not copy other people\u2019s work, please do your own work on the
project. I will be checking for evidence of cheating on this project and any students caught will receive a score of zero on this project. You will also have some
questions on the midterm based on this project so it is to your advantage to complete this project yourself.<\/p>\n

3. You need to turn in the completed assignment through blackboard in order to get credit for this project.<\/p>\n

Note#1: You can shorten your typing time on this project by left clicking on cell A1 in the table above and dragging your mouse over this table to cell F10. Then
right click on the highlighted data and choose copy. Then open up Microsoft Excel and right click on cell A1 in Excel and choose Paste.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

ITM Here is some of the criteria: And attached is the assignment. 6 Interest 1 points paid 360 loan term not paying up front Actual interest rate = B1-(lookup(B2,Sheet2!A3:B9)) B7= IF(B6=0, B8*B2%,0) B9= B7+B8 B5= PMT(B3%\/12, B4, B9) B11=B9 C11=B5 Read More …<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-126480","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/126480","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=126480"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/126480\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=126480"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=126480"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=126480"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}