It is not trivial, but doable.
These few days I have been busy setting up GnuCash with intention to dump MS Excel. As usual, the journey wasn't smooth-sailing.
The first problem I encountered was there doesn't seem to be any way to automatically manage FDs (Fixed Deposits, it is called Certificate of Deposit, CD in other countries).
Scenario: I have a amount p of money as FD. The FD will mature every n month and interests generated will be reinvested at the prevalent rate.
What I want: GnuCash to automatically renew the entry at the maturity date. As long as the FD is valid, GnuCash should take care of that until being told otherwise.
As usual, good news and bad news:
Bad news:
Surprise surprise, it seems GnuCash can't do this by default. There are druids to calculate mortgages and loans, but none for savings and FDs. (I checked the wiki and help files on gnucash.org, to no avail. In fact, the formula gnc:computerInterestIncrement on the wiki FAQ is wrong). You can verify it with any financial calculators.
Good news:
The underlying toolchain is written in scheme. This makes GnuCash very flexible and hacking possible. What follow are some pointers.
Assumptions:
- You have read the GnuCash help files and know the concept of debit and credit
- You know how to create accounts under GnuCash
- You already have at least one FD account under Assets and one under Incomes. If your bank charges you on this, add one more account under Expenses and the following steps need some adjustments.
- Exit GnuCash
- Edit fin.scm (inside /usr/local/share/scm if you use the default configuration)
- Add the following at the start of the file, right under the comments (comments are lines starting with semicolons ';').
- Note: Blogspot has broken some of the comment lines, please fix those by yourself.
- Start GnuCash, and Actions->Scheduled Transaction Editor. Click on 'new'.
- Enter a name and set the date according to the maturity of your FD.
- Under 'Template Transactions', enter a description, then click on the 'Enter' icon. We need 2 action items: one for debit and one for credit. If you are not sure about this concept or how to do all these, please refer to the GnuCash help files.
- At the debit field, enter 'interestYield(p:interest_rate:compound_number:i)' where p is your principal amount, interest_rate is the current rate entered as-is (if it is 3.3, just enter 3.3, my function will do the conversion accordingly), compound_number refers to how frequent interest is compounded (monthly -> 12, quarterly->4, semi-annually->2, annually -> 1), i is the i-th occurence of the compound event. This variable can be entered as-is and no need to substitute it.
- Examples: Suppose I have principal amount of 1000 to invest. Interest rate is 5% and will be compounded monthly. Hence I would enter:
interestYield(1000, 5, 12, i). If the interest is compounded yearly, then I would have entered interestYield(1000,5,1,i). The rest is similar. - Enter the same formula for the credit field.
;
; By HKC Dec 02, 2006
;
(define (futureValue p r n i)
(let ((rate (/ r 100)))
(* p (expt (+ 1.0 (/ rate n)) i))))
;;
;; This function calculates the interest yield for the i-th month.
;;
;; Example: With principal of 10000, interest rate 10%, and interest ;; is calculated and reinvested
;; monthly at the same rate, we want to know how much money we ;;can get during the 8-th month.
;;
;; Invoke (gnc:interestYield 10000 10 12 8) and you should get 88.31767424426289
;;
;;
(define (gnc:interestYield p r n i)
(let ((this-val (futureValue p r n i))
(prev-val (futureValue p r n (- i 1))))
(- this-val prev-val)))
;;
;; This function calculates total interest accrued, assuming all ;;interests are reinvested at the same rate.
;;
;;
(define (gnc:interestAccrued p r n i)
(define (sum-it sum i)
(if (= i 0) sum
(sum-it (+ sum (interestYield p r n i)) (- i 1))))
(sum-it 0 i))
I wrote this in quite a rush. If there is something unclear, please let me know and I will be happy to clarify.
No comments:
Post a Comment