How To Use Financial Functions in Google Sheets

The article demonstrates how to use financial functions in Google Sheets including standard financial and custom functions.

Like the desktop spreadsheet functions, Google Sheets functions can be used to build formulas that manipulate data and compute strings and integers.

Google Sheets has standard functions. Follow the link to find them. Also, you can create custom functions in Google Sheets.

How to use the GOOGLEFINANCE() function

You can use the GOOGLEFINANCE() function to retrieve live stock price or currency exchange rates.

How to retrieve the currency exchange rate

Suppose you are a Japanese businessman and you have a business relationship with a businessman from the USA. You received some goodies, sold them, and you know how many yen you got for the past 14 days. Your partner wants to receive the day-by-day report of the income in US dollars.

To solve the above task do following:

Open Google Sheet, create a sheet and prepare the empty table:

Then in cell A3, enter the formula:

=GOOGLEFINANCE("USDJPY","price",A1,B1)

You expect to see this result:

To finish the task, you need to enter the income in column B and the formula =C4/B4-in column D.

How to retrieve history of the stock prices

Suppose you obtained the stock share of Harley-Davidson (stock symbol, or ticker symbol: HOG) and you are wondering how your capital has changed over the past two weeks. You can do this task easily using the GOOGLEFINANCE function.

We omit all the steps described above. Prepare the table in the same way and write in the cell A3 the formula:

=GOOGLEFINANCE("HOG","price",A1,B1)

Finally you have this result:

How to use the function DB

The DB function calculates the depreciation of an asset for a specified period using the arithmetic declining balance method.

Let us calculate the balance cost of the server DELL A9510443 after 7 years consuming. And hence prepare the input data table and output table for calculation:

Then insert in the cell B10 the formula:

=IF(A10>$B$5, 0, DB($B$3,$B$4,$B$5,A10,$B$6))

Copy the cell B10 to the cells B11:B16. You expect to see this result:

How to create custom financial function

G Suite allows you to create custom functions, including financial functions. You can create custom functions using App Script. To learn how to create and use App Script, follow the link.

Click the menu item Extensions > Apps Script. Then open the Code.gs file and write in it:

/**
 * Calculates the sales tax by states.
 * 
 * @param {location} alpha code of a state.
 * @param {input} sales amount.
 * @return Sales tax amount.
 * @customfunction
 */

function taxByState(location, input) {
  var saleTaxRates = new Map([
    [ 'CA', 13.3 ],
    [ 'HI',  11],
    ['NJ', 10.75],
    ['OR', 9.9],
    ['MN', 9.85],
    ['DC', 8.95],
    ['NY', 8.82],
    ['VT', 8.75],
    ['IA', 8.53],
    ['WI', 7.65],
    ['TN', 9.55],
    ['LA', 9.52],
    ['AR', 9.51],
    ['WA', 9.23],
    ['AL', 9.22]
    ]);

  location = location.toUpperCase();
  var rate = saleTaxRates.get(location)/100;

  return (input * rate);
}

After that create following table in the Google sheet:

Finally, enter the following formula in the cell C2:

=taxByState(A2,B2)

And the copy it to the cells C3:C16. You expect to see the result:

Was this helpful?

1 / 0

#financial function #Google sheet