Excel 2010 PMT (Payment) Function

Excel Payment (PMT) function is extremely useful when you need to know the monthly payment amount on full loan/credit payment, it actually evaluates amount to be paid monthly on the basis of credit amount, interest amount, and time. The basic parameters of this function are rate, nper (number of payments), pv (present value), and fv (future value), so if you need to calculate an amount which also considers these parameters, then this post will help.

To start off with, launch Excel 2010, and open the datasheet on which you want to apply PMT function. For instance, we have included a datasheet, containing fields; ID, Price, Interest Rate as shown in the screenshot below.

Now we want to add a monthly payment, for this we need to set the rate we will be charging, so for this we assume that total payment will be paid by the client in 2 years (2*12=24). The Interest rate we are charging is 8%, so will add a new field by the name of Monthly Payment.

Now we will add a PMT function that will evaluate how much each customer has to pay including interest.

The Syntax of PMT function is;

=PMT(rate, nper,pv,[fv],[type] )

In formula parameters, rate refers to, on which rate interest will be charged, nper stands for number of payments, pv refers to Present Value, you can also mention future value and type, but in our case we will leave these values.

So the rate we will be charging, is the sum of interest rate divided by 12 (number of months), clients need to pay us total amount + interest in 2 years so the nper value will be 12*2. The present value will be the price of the Product or Service.

So will we write the formula in this way;

=PMT(D3/12,2*12,C3)

The location D3 in the table refers to interest rate, and C3 in the datasheet refers to Price of Product/Service. When the function will be evaluated, it will yield $63.32 as shown in the screenshot below.

Now apply it over the whole Monthly Payment field by dragging the plus sign at the end of it’s cell to the bottom of the column.

In Excel, there is another way of applying PMT function, if you want to evaluate PMT function exclusively without creating fields, then select any cell where you want to show the result, navigate to Home tab, from Auto Sum options, click More Functions.

You will reach Insert Function dialog, search for this function by providing PMT keyword, select the function name and click OK.

Upon click, you will see the dialog asking you to enter values; Rate, nper, pv, fv, etc. Enter in the desired values as shown in the screenshot below, and click OK.

Now you can see the result, Excel automatically puts-in the values in the selected cell.

Read more

Excel 2010: Convert Number Values Into Time

There are many ways in which you can enter time into cell, but Excel has an built-in function which facilitates users to convert values into time format, by using this function you can convert data in hrs:mm format, and hrs:mm:sec format. It automatically shows military time format against the suitable values in the cell. This post covers how to change values into time format.

Launch Excel 2010, open a datasheet in which you want to convert values to time format.

We are intending to convert values in the Time (Values) field into hh:mm, now we will be adding another field with a label Time (hrs:mins). We will be writing a simple formula in the first row.

Now lets add the formula;

=B2/(24)

In formula, we are dividing time by 24 (24 hrs sums up a day). This will yield value in decimal.

Now select the cell that contain result, and right-click to select Format Cells.

Format Cells dialog will appear, from the left pane select Custom, and from the right pane under Type, look for h:mm AM/PM, and click OK.

Here you can see in the screenshot below, that the value is now converted into Time format.

Now drag the plus sign at the end of the cell towards the end of the column, to apply it over the whole field.

Read more

Camera Tool Function In Excel 2010

With Excel 2010 Camera tool, you will be able to take a snapshot of datasheet portion. This enables you to place a live picture anywhere in the worksheet, you can apply different styles, colors, and designs over it. Since it is live image so it syncs with the datasheet range on run time.

Launch Excel 2010, In order to use this feature, you need to make Camera button appear on the Excel Quick Access Toolbar, because it is not present by default. Head over to Quick Access Toolbar and from small drop down button, click More Commands.

You will reach Excel Options dialog box. Hit drop-down button from right pane present beneath Choose commands. Click All Commands to show all commands which Excel contains. Now scroll-down the list and find Camera command. Select it and click Add (>>) to show it on Quick Access toolbar pane. Once added, hit OK.

You will Camera button on the quick access toolbar.

Now for using this feature, select the portion of the datasheet for which you want to take a snapshot of, and click Camera button.

Now you are ready to take a snap, just click on the location where you want to show the image.

Now when you will select this image Picture Tools tab will appear, from where you can apply different styles and designs.

So apply design and styles which best suites your datasheet image. You can apply shadowing, 3d effects, rotation, colors etc.

Most importantly, it is not a mere image of the table, but it syncs with the table values on run-time, means on changing any value in the datasheet, reflection can be seen in the image. As you can see on changing value and color of the cell, changes appear in the image.

Read more

Outlook 2010: Apply Rules on Text Messages (SMS)

Outlook Rules Wizard lets user apply new rules over sending and receiving emails, but it would be even more useful to apply rules, when you have subscribed to a Text Message service, which integrates with Outlook 2010. This post will cover how you can apply rules on text messaging, with a simple example.

Launch Outlook 2010, make sure that your Text Message account is properly configured. Now navigate to Home tab, and from Rules drop down options, click Manage Rules & Alerts.

Rules and Alerts dialog will appear, choose the desired account on which you want to apply rule, and click New Rule.

We will select Apply rule on messages I send, and click Next.

Since we want to send message to others so in this step we will click Next without enabling any option. Click Yes to continue.

In this step, you can associate a lot of actions, but we will enable notify me when it is delivered, and Cc the message to people or public group option, from the bottom pane click people or public group to add email address of the recipient, as shown in the screenshot below. Now click OK , and then finish the wizard

Apply the newly created rule. On sending the text message, you will be notified and Outlook will automatically send the copy of message to the specified recipients, as you can see in the screenshots below.

Read more

Adding Outlook Email Tool In Excel 2010

Sometime it happens that you need to send Excel worksheets via email, but for that you don’t need to follow the orthodox way of sending datasheet by attaching it with email. Excel provides a tool, which enables you to send it over with a single click. This post illustrates how to make Email option apparent in Excel 2010, and how to use it.

Note: This procedure requires Outlook to be installed and configured on your computer

Launch Microsoft Excel2010, go to File menu, click Options, and in left pane click Customize Ribbon, in right pane select All Commands from Choose commands drop-down options and look for Email, Email as PDF Attachment, and Email as XPS Attachment commands. Now from left pane, hit New Tab, change it’s name to Outlook, and then click New Group to create group in the newly created tab, give it an appropriate name. Now click Add >> button to add Email-related commands to newly created group. Click OK to close Excel Options dialog.

Now navigate to newly created Outlook tab, you will see commands added earlier.

Now open the datasheet which you want to attach with an email.

If you want to attach datasheet as default Excel format (.xlsx), then click Email on Outlook tab.

Outlook mail window will open, you can see the attached Excel datasheet. Now you just need send it to the recipient.

For sending datasheet as PDF copy, click Email as PDF Attachment on Outlook tab. Outlook mail window will open, showing PDF format as attached file.

Upon double-click the attached file, you can open the datasheet in PDF format to verify the data positioning etc.

Read more

Track Changes In Word 2010 Document

If you want to see the old version of the document, or revert to the original document, click Original.

Original document will appear (before any changes).

Read more

Send Outlook Mail to OneNote 2010

Outlook 2010 has an intrinsic feature which lets user to transfer mails to OneNote 2010, it could be very useful, if you are using OneNote 2010 for taking notes, by sending email content, you will able to complement the note which is related with an email.

Launch Outlook 2010, and open the mail you want to send to OneNote, as shown in the screenshot below.

Now navigate to Home tab, and click OneNote

Select the location where you want to place the email in OneNote sheet, if you are having a lot of sheets in OneNote, you can also search them by providing search keyword. Click OK after selecting one.

Upon click, it will automatically send all the related email content; subject, sender email address, date/time, to selected OneNote sheet as shown in the screenshot below.

Read more