Overview

In this article, we’re going to be covering the last 4 functions in the "Text and Data" set of formulas for SharePoint.

The four functions we’ll be discussing are:

DOLLAR – Converts a given number into text with it represented in a "Currency Format" and having it’s decimals rounded to a specified place. The format used in this function to convert the number to the appropriate text convention is "$#,##0.00" (this text format is automatically applied – so you don’t have to specify it directly)

USDOLLAR – Same as the "DOLLAR" function in that it takes a given number and converts it to text (into a "Currency Format" with the decimals rounded), but it differs in that it will use a standard "U.S. Currency" format instead of what your local computer may be set to (international settings).

FIXED – Also similar in how both the "DOLLAR" and "USDOLLAR" functions format a number into text, but differs in that the "FIXED" function simply formats the text result into a decimal format using a period and commas (not necessarily currency – this will just represent the text version of a number, whereas the "DOLLAR" and "USDOLLAR" will pre-pend on a "Currency" symbol to the result – i.e. "$").

VALUE – Converts a text string that represents a number into a number (the first three described above all take a number and convert it to text – this one works the other direction taking text and converting it to a number).

Example of these functions would be:

Text: "12345"
Formula: DOLLAR("12345")
Result: $12,345

The above will return a currency-based result displayed in the currency-specific regional settings you have applied to your local computer (which may be overridden by whatever regional settings applied in your instance of SharePoint).

Text: "12345"
Formula: USDOLLAR("12345")
Result: $12,345

The above will return a currency-based result that uses the standard U.S. Currency convention.

Text: "12345"
Formula: FIXED("12345")
Result: 12,345

The above will return a numerical result in a 3-character-grouped-comma-separated-2-decimal place format (that was a mouthful).

Text: 12345
Formula: VALUE(12345)
Result: 12,345

Although appearing the same as the original input (text), the outputted result of this is a number converted to a standard numerical format which includes a comma ("#,##0.00").

Getting Started

To see how these can work in a SharePoint list, we’ll look at the example of a "Loan Calculator" used to determine monthly payments on an amount of money borrowed.

For this, we’ll create a custom list that will allow us to build out our calculated columns and test how these functions work (we’ll look at each one as we create the columns).

In SharePoint, create a "Custom List" called "Loan Calculator".

Once created, we’ll create several new columns in order to have the list perform the calculations we need.

First, to have the items that will appear on the list make a bit more sense, simply go into the list settings ("Settings > List settings") and rename the "Title" column to "Loan Title".

Next, we’ll create our columns using the following:

Create a new column called "Years" making it a "Number" type. Give it a description of "Total Number of Years on the Loan" and set its "Number of decimal places" to "0" and uncheck "Add to default view" (we’ll be displaying it in another column).

Create another new column called "Payments per year" making it a "Number" type. Give it a description of "Total payments per year (monthly payment, weekly, etc.)" and set its "Number of decimal places" to "0" and uncheck "Add to default view" (we’ll also be displaying it in another column).

Create another new column called "Annual Rate" making it a "Number" type. Give it a description of "Annual percentage rate on loan" and set its "Number of decimal places" to "4", and check the box to "Show as percentage".

Create another new column called "Loan Amount" making it a "Single line of text" and give it a description of "Total loan amount (in dollars and cents)".

Create another new column called "Total Payments" making it a "Calculated" type. Enter in the following formula and set its return type to "Number" with "0" decimal places:

Formula:

=Years*[Payments per Year]

This calculation is simply multiplying the number of years of the loan, by the number of payments per year.

Example:

Years = 10
Payments per year = 12
Formula = 10*12
Result = 120

Create another new column called "Payment Rate" making it a "Calculated" type. Enter in the following formula and set its return type to "Number" with "Automatic" for the decimal places (we don’t know how many it will have yet, so this will leave plenty of room for us), and uncheck "Add to default view" (we’ll be referencing this column in another calculated column so it doesn’t need to be displayed on its own):

Formula:

=[Annual Rate]/[Payments per Year]

This formula will take our "Annual Rate" and divide it by the total payments per year to give us the total of the rate that will be applied each month.

Example:

Annual Rate = 5.5%
Payments per year = 12
Formula = .055/12
Result = .004583333

Create our final column called "Payment Amount" making it a "Calculated" type. Enter in the following formula and set its return type to "Single line of text":

Formula:

=FIXED(-PMT([Payment Rate],[Total Payments],[Loan Amount]))&" ("&[Payments per year]&" times per year for "&[Years]&" years)"

Note – since we’re working with money in this exercise, we’re also taking advantage of one of the available functions in the "Financial" set of formulas for SharePoint called "PMT". I’ll be discussing this in future articles when I delve into this set of functions, but since we’re using it here, the "PMT" function is used to calculate the payments for a fixed-rate loan using regular or "Constant" payments and a constant interest rate.

The function has the form of:

PMT(rate,nper,pv,fv,type)

Rate – interest rate for the loan
Nper – total number of payments for the loan
Pv – principal (present value, or total amount that a series of future payments is worth now)
Fv – future value, or cash balance you want to attain after the last payment is made. This is an optional field in the function and will be presumed to be "0" if omitted.
Type – either a "0" or "1" indicating when payments are due (0 = end of the payment period, 1 = beginning of the payment period. Presumed to be "0" if omitted since this value is also optional in the function)

Dissecting the Formula

In our formula for the "Payment Amount" column, we can look at it as:

=FIXED(-PMT([Payment Rate],[Total Payments],[Loan Amount]))&" ("&[Payments per year]&" times per year for "&[Years]&" years)"

The "PMT" function is in the format of: PMT(rate,nper,pv,[fv],[type])

Note – items in brackets [] are optional (we’re not using them in this case).

Stepping through the calculation, we first calculate the current payment by the following:

(Original Principal)*((1 + interest per period)^(number of periods)*(interest per period)/((1+interest per period)^(number of periods)-1)

Example:

(P)Original Principal = 250,000
(I)Interest per period = (Annual rate)/(payments per year) = .055/12 = .00458333
(N)Number of periods = 360 (based on 30 year loan with 12 payments per year: 30*12=360)

=(P*((1+ I)^N)*I)/((1+ I)^N-1)
=(250000*((1 + .004583333) ^360)* .004583333) /((1 + .004583333) ^360 -1)
Result = -1419.472441

Since this number (-1419.472441) needs to be reflective of a dollar-type number, we take it and pass it to the "FIXED" function to set it as a number with only two decimal places and a comma.

=FIXED(Result) = -1,419.47

Also, since this specific calculation is returning a negative number, we use the "FIXED" function with a minus (-) in front of the data it needs to format:

=FIXED(-Result) = 1,419.47

After we have our value, we then "CONCATENATE" a little more information onto the final result in order to make it more easily understood (optional, but can assist in readability):

=FIXED(-Result) ]))&" ("&[Payments per year]&" times per year for "&[Years]&" years)"

This produces a final result of:  "1,419.47 (12 times per year for 30 years)"

Testing the Solution

Now that we have our columns setup, let’s create a new item on our list and see what happens.

On the list, create a new item filling in each of the fields with data:

Loan Title = Home Loan
Years = 30
Payments per year = 12
Annual Rate = 5.5
Loan Amount = 250000

Upon saving the item, we’ll see the calculations have taken place and show the results:

• "Annual Rate" is displayed as a percentage.
• "Total Payments" shows how many payments it will take to pay off the loan.
• "Payment Amount" displays how much is due each payment with frequency.

This works, and does give us the accurate calculations of the loan and payments, but notice how the "Loan Amount" and "Payment Amount" values are represented a numbers, but not in a "Currency" format? Let’s go back to our column settings and make a few changes to make the display of these values more relevant to the task (this is money we’re working with, so it should look like money!)

In the list settings page, click on the "Loan Amount" column to edit its properties. Change its type to "Currency" and leave the rest as default. Once saved (click OK on the prompt warning you about the possible loss of data – we wont be losing anything), go back to the list and view the item again.

The "Loan Amount" column now reflects a currency format for its value (we could’ve just used this format when we originally created the column, but I wanted to show that in many cases you can change it after the fact without any consequences).

Next, we need to look at the "Payment Amount" column and modify it so the value displayed will also be in a currency format.

Looking at our formula again:

=FIXED(-PMT([Payment Rate],[Total Payments],[Loan Amount]))&" ("&[Payments per year]&" times per year for "&[Years]&" years)"

All of our numerical formatting is being applied based on the first function "FIXED". Since that function formats the text into a standard numerical format with commas and a fixed decimal length of two places, it’s actually using the "TEXT" format of "#,##0.00" (Seem familiar? Look back at the beginning of this article in the description for "DOLLAR").

As a test to see if this is accurate, modify the formula in the "Payment Amount" column to be the following:

=TEXT(-PMT([Payment Rate],[Total Payments],[Loan Amount]),"#,##0.00")&" ("&[Payments per year]&" times per year for "&Years&" years)"

Notice any difference in the values being displayed for the item? You shouldn’t because the formatting will be the same.

Modify it again, but this time, add in the dollar sign "$" in the formatting:

=TEXT(-PMT([Payment Rate],[Total Payments],[Loan Amount]),"$#,##0.00")&" ("&[Payments per year]&" times per year for "&Years&" years)"

Now, when looking at the results, we’ll see the value displayed in a currency format:

Again, this does work, and is a manner in which you can have complete control over the formatting of the results, but since we’re just working with the "Currency" format, we can skip this method and instead use the built-in shortcut functions that deal with currency directly. These are of course, "DOLLAR" and "USDOLLAR".

In my case, since I do work in the States, and my international settings for both my PC and my instance of SharePoint are also set for U.S., using either function on my machine will both produce the exact same result. The idea though, is that if you are on a machine that has International settings other than those for the U.S., using "DOLLAR" will display the result in the local currency format (that which is specified on your local machine), whereas using the "USDOLLAR" function will always produce results formatted to meet U.S. Currency.

To use these, modify your formula accordingly, depending on which you want:

=DOLLAR(-PMT([Payment Rate],[Total Payments],[Loan Amount]))&" ("&[Payments per year]&" times per year for "&Years&" years)"

=USDOLLAR(-PMT([Payment Rate],[Total Payments],[Loan Amount]))&" ("&[Payments per year]&" times per year for "&Years&" years)"

So, as I’ve stated in several articles, there a many different ways of accomplishing the same task within SharePoint, it just depends on your needs, the amount of control you want to have, and what the result should be. Each of these approaches will all do the job, but may differ in their results somewhat depending on regional location, personal customizations, and how you approach the formulas, but they do all work, and I’d encourage you to work with each of them to see what other differences you may find.

The last function I want to mention is the "VALUE" function. This function is used to convert text that represents a number, into an actual number. It works with each of the three standard numerical formats (constant number, date, time), and will return an error ("#VALUE") if the text is not in one of these.

Examples:

Text = "12345"
Formula = VALUE(Text)
Result = 12,345

The above returns the formatted number 12,345 since the inputted text represented a number. Also, notice how the result comes through formatted with a comma? Once again, we find another function using the same formatting type of "#,##0.00".

To prove this, look at the following examples:

Text = "12345.99"
Formula = VALUE(Text)
Result = 12,345.99

Text = ".0599"
Formula = VALUE(Text)
Result = 0.0599

Notice how each result gets formatted (note the added leading zero on the second example)?

Summary

Part of the reason I grouped these four functions into the same article is because of this commonality they each have in their formatting of the results. Each uses as it’s base format, "#,##0.00", but can differ somewhat depending on the situation (regional settings).

Granted, we did actually cover a fifth function "PMT" in considerable detail (which we will be looking at more when I get to the series that covers that set of functions), but it seemed necessary since we were working with a set of formulas involving money, so you got one for free!!

This is the last in the series dedicated to the "Text and Data" set of functions as we’ve now covered each function in the set, but we will be revisiting many of these in future articles since this particular set is probably the most frequently accessed (and questioned about).

Till next time…

- Dessie

Suggestions for future posts on calculated columns are always welcome, and in fact are encouraged.

Some of the best scenarios to illustrate are the "real-world" problems that we each face day to day, so if you have an example, an idea you want to explore, or a "Can this be done with a Calculated Column?" question that I can use as the topic of a future post, please submit it as a comment below and I’ll see what I can do to work up a post covering it.

Dessie Lunsford
Points-of-Sharing