A LibreOffice Calc formula to calculate interest rate equivalencies

To accurately compare rates (profit, interest, et cetera), you must first bring these rates to a common time standard.  E.g. a daily rate that accrues daily with an annual rate that accrues monthly cannot accurately be compared.  However, if there was a formula to bring the annual rate to a daily equivalent rate, you'd be okay.  In other words: accurate financial sense requires apples-to-apples comparisons.

This is why I've written code to do exactly that:

REM EQUIVRATE converts a rate to the equivalent
REM rate compounded over nper periods.
REM
REM rate: the effective rate
REM nper: the number of periods, can be fractional
REM
REM Compute a yearly rate of 10% compounded over 2 years:
REM EQUIVRATE(10%, 2)   -> 21%
REM
REM Compute the annual rate equivalent of a 21% biennial rate:
REM EQUIVRATE(21%, 1/2) -> 10%
REM
REM Compute a daily rate of 0.1% compounded over 365 days:
REM EQUIVRATE(0.1%, 365) -> 44.02513%
Function EQUIVRATE(Optional rate as Variant, Optional nper as Variant)
        Dim rate_d as Double
        Dim nper_d as Double
        If IsMissing(rate) or IsMissing(nper) Then
        	EQUIVRATE = "#BAD ARGS!"
        ElseIf IsNumeric(rate) And IsNumeric(nper) Then
	        rate_d = Val(rate)
    	    nper_d = Val(nper)
    	    On Error Goto Overflow
        	EQUIVRATE = (rate_d + 1) ^ (nper_d) - 1
       	Else
        	EQUIVRATE = "#BAD VAL!"
        End If
        Exit Function

	Overflow:
		EQUIVRATE = "#" + CStr(Error) + "!"
		Exit Function
End Function

Put this formula code in your Calc spreadsheet's macros like so:

1. Copy the formula text to your clipboard.
2. On your spreadsheet, click the Tools menu, then click Macros, then click Edit macros...
3. In the macro editor that opens, on the left sidebar, locate your spreadsheet and expand it.  You'll see a Standard entry.
4. Click on the Standard entry and a text editor will appear on the right side.
5. Paste the macro text there.
6. Close the macro editor now.

OK, you're ready to use the formula.  For example: suppose you want to calculate what the equivalent daily-compounded daily rate is, for an annually-compounded 10% annual rate.  That should be pretty easy — just enter the formula =EQUIVRATE(10%, 1/365) in a cell, and you'll get the value right away.

Now, suppose we take this example:

• You have an investment that requires you to put up 100 dollars, and promises to pay 35% every year.
• You have an investment that requires the same capital, and promises to pay you 15% every two months.
• We stipulate that both investments are equally risky, so we will apply no discount to the future values.

Which one is the better investment?

For investment A, we will leave it as such.  It's 35% yearly.  Yearly interest compounded yearly sounds pretty reasonable as a comparison basis.

For investment B, we will discover what you actually get every year from that 15% every two months.  Use the formula =EQUIVRATE(15%, 12/2), where 12/2 represents the fact that your investment will compound six times over that year (there are six 2-month periods in one year).  The result is around 131.31%, which clearly indicates that B is way superior than A.

What if you wanted to take the equivalent daily rate of both investments?  The equivalent daily rate is more or less by how much percentage your investment would increase every day, when compounded daily.  That's easy with this formula too:

• Investment A: =EQUIVRATE(35%, 1/365) -> 0.0823%
• Investment B: =EQUIVRATE(15%, 1/60)  -> 0.2332%

By this daily earning accrual standard, investment B gives you about .23% daily, investment A only .08% daily, and therefore investment B is still superior.

What if you wanted to project how much money you'll make to the end of the year, based on the current value of your $10000 investment mid-year, and the value of your investment at the beginning of the year ($5000)?  Assuming a constant progression and daily accrual of earnings (which means your earnings are compounded daily), this is easy to do.

First, compute the "rate" at which you've gained so far, which is trivial enough: it's 100% profit over 180 days.

Now let's find out what that "rate" is, daily.  Plug into the formula:=EQUIVRATE(100%, 1/360) which will immediately show about 0.39% daily.  Let's assume this value is in cell A7 of your spreadsheet.

Now it's a matter of plugging the future value formula: =FV(A7,180,0,-10000) meaning you find yourself mid-year with $10000, and you want to find out what that will be over the next 180 years with the interest rate you just computed.  This formula will now show you a nice round $20000 as the future value.  So, basically, from $5000 you can end up with $20000 if your investment yields 0.39% daily compounded daily.

I'll leave as exercise for the reader to use the formula to find out what that daily rate actually is, when transformed to a yearly rate compounded yearly.