EduSarva

CUMIPMT Cumulative interest payment function allows you to calculate the interest paid for a loan or from an investment from period A to period B. When getting a loan, CUMIPMT function can be used to calculate the total amount of interest paid in the first five months or from period 12 to period 20. A period can be a month, a week or two week. Loan Amount : 350,000.00 APR: 4.5% Down payment: 0.00 Years: 25 Payment per year: 12 From the above data, we can calculate the following: No of Period: 25 × 12 = 300 Periodic Rate: 4.5/12 = 0.375% Here is how you will substitute these values into the function. = CUMIPMT (periodic rate, No of period, vehicle price, start period, end period,  ) = CUMIPMT (0.375, 300, 350000, 1, 5, 0) In an excel worksheet, we use cell address instead of actual values as shown below: Here is the formula view of the worksheet: CUMPRINC Another related function is CUMPRINC. CUMPRINC function is used to calculate c...

Working with multiple worksheets Excel has a great feature that allows you to work in multiple worksheets simultaneously. This feature allows you to add content and/or format the contents in the multiple worksheet at the same time. Let's look at an example where this feature can be a great time saver. Let's say you have a yearly budget for five years and the data for each year is placed in a separate worksheet. For each year the structure of data set, column heading and row heading are the same. It has cell formatting and number formatting consistent across all worksheets. Now let's say that you want to modify all 5 worksheet simultaneously. This is when the grouping worksheets become very useful. Instead of modifying one worksheet and then copying the changes to other worksheets, you can modify all worksheets at the same time. To do this first group the worksheets that you want edit simultaneously. To group worksheets To group worksheets together use one of the...

Excel allows you to import data from various file formats. You can import data from websites, text files, XML files and many types of database files. Here is a simple text file that contains the following data. In this dataset, each field is separated by a comma and each record starts in a new line. To import above text file into an excel worksheet: 1. Select the cell where you want the data to be pasted. 2. Click the Data tab > click From Text >   Text Import Wizard dialog box opens 3. There are two options here, keep the default option selected. Check My data has headers . 4. Uncheck Tab and Select comma. Click Next. 5. In step 3 of the Text Import wizard dialog, select the first column and select text option, select the second column and check the text option and repeat for the third. 6. Click finish. A Import Data dialog appears, click OK to accept the default. 7. Now you should have successfully imported the comma separ...

Drawing AON diagram in Excel Excel can be used to draw AON when you don't have a specialized software to draw your network diagram. First create a node template that can copied again and again. This can be created using cell formatting features such as borders and merge cells. Highlight any 3 by 3 cell group in the worksheet as shown below and then apply All Borders  cell formatting. Now select the three cells in the middle as shown below and select  Merge & Center.  You can now copy and paste this template, for each node you need in your network diagram as shown below. Then use arrow shape in Insert > Shapes > Lines to draw the arrows.