2023 Hello SolutionsPro here is Week 5 COMP 102 assignments Thank you Part | Assignment Collections

          Excel has so many functions that I’m not sure that any one person has a practical use for all of them. For me, though I use Excel regularly, most of the functions in the engineering and financial sections of the assigned reference have very uses. For someone in those industries, I’m sure they are very valuable functions. As I do manage budgets in excel, I took this opportunity to explore the financial section to see if anything popped out.

          A set of functions within the financial section seemed like a great tool for looking at loans and interest. If, say, I wanted to buy a house or get a business loan I would need to be prepared with more exact loan, interest, and payment information than I have time to calculate longhand. In order to show this usage and know that I was operating the functions correctly, I created a test spreadsheet to calculate loans which I have attached to this post.

          To make my calculations I used the “CUMIPMT” function (possibly short for Cumulative Interest Payment) and the “CUMPRINC” function (possibly short for Cumulative Principal) (Microsoft Corporation, (n.d.). With these functions, Excel can calculate how much a loan payment will be, how much principal is paid from each payment, and how much interest is paid from each payment. It also asks for a payment range so it could return the sum of all payments in that range.

          The arguments for both of these functions are the same. (Function arguments are a comma delimited series of the information needed for the function to work.) The arguments are as follows:

1.                 Interest rate.

2.                 Total number of payments.

3.                 Total amount of the loan.

4.                 First period to be counted.

5.                 Last period to be counted (can be the same as the first if you only want to see one payment).

6.                 Timing of the payment within the period.

          While testing, I came across an interesting note about these functions. Both functions result in a negative number as Excel assumes you intend to subtract the result out of the beginning sum. For the purposes of my test, I thought a negative number looked tacky, so I use the “ABS” function to return the absolute value of the result.

          To make my test sheet more variable, I wanted to allow for a change in the number of years of the loan and the payments per year. As I filled the formulas down to as many cells as it would take for a monthly 30 year loan, I got errors from the payment number exceeding the total number of payments in my original formula so also had to escape the errors by replacing non-number values with a 0 in the calculations. I was able to do this with the standard “IF” function combined with the “ISNUMBER” function. Also, in order to allow for a varying number of payments per year, I used the “DATE” function to calculate the date. This was done by the simple math of adding the appropriate number of months to the original date.

          Using these functions was reasonably simple and now I can see what a loan would be when I change any of the terms of the loan. Everything is calculated immediately when I make a change, so looking at a wide variety of options is a simple task. Please check out my test Loan Calculator and let me know what you think. Feel free to play with it and let me know if you have suggestions that would make it even more functional. 

Thanks

David 

References

Microsoft Corporation. (n.d.). Excel Functions (by Category). Retrieved on April 21, 2014 from http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP005204211.aspx

David Hollingsworth                          

The two functions that I found interesting were named EVEN and DOLLARDE. With function EVEN you have the ability to round the number to the nearest even integer. You can use this function for processing items that come in two. Example A packing crate accepts rows of one or two items. The crate is full when the number of items rounded up to the nearest two match the capacity of the crate. And with DOLLARDE you can change a fraction to a dollar sign and most businesses need tools like this because of inventory and the cash. It Convert Security prices to decimals.

Retrieved from http://office.microsoft.com

Part III Week 5 Individual Project:

This assignment will use the grade book you created in the Unit 4 Individual Project. Click here if you need the original template. Open the spreadsheet, and add a column bar chart showing your grades. To complete this, do the following steps:

• Step 1: Open your Unit 4 Individual Project assignment (grade book).
• Step 2: Using your cursor, highlight the course names and the data that is next to them.
• Step 3: Click on Insert > Charts, and select “Column Chart.”
• Step 4: Click on the chart, and select “Cut.”
• Step 5: Go to the bottom of your spreadsheet, and select Sheet 2. Click on a cell within that sheet.
• Step 6: On Sheet 2, paste the chart you just copied.
• Step 7: On the tabs at the bottom, label Sheet 1, as “Data” and Sheet 2 as “Grade Chart.”
• Step 8: Add a title to the Grade Chart.
• Step 9: Revise the chart as needed to make it look more presentable.
• Step 10. Save your work as “First Name_Last Name_StudentGradebookIP5.”
• Step 11: Turn in the assignment to your instructor for grading.

Please submit your assignment.